关于数据库日期时间的札记

  使用关系数据库这么多年，感觉对日期和时间这种类型数据字段的处理和存储是最为繁琐的，不同类型的数据库不尽相同，风格各异。记忆力不好，每每忘记都要在网上上下求索，效率极低。鉴于此，特将一些心得、要点记录下来，以便日后参考。其实，无论是date、time抑或是datetime，需要弄清楚的地方无非只有两个方面：存储时间日期的数据类型，以及对其的各种操作和相关函数，比如提取年月日、转化为格式化字符串、日期时间的加减运算，等等等等。弄清楚了这些后，时间日期对于数据库“小牛”们真的仅仅就是“a piece of cake”了。
   本文主要讨论我平时用得最多的三种数据库SQLite、Mysql和Oracle。

  作为轻量级数据库的杰出代表，SQLite对于时间日期的处理也相当轻便灵活。对于数据类型，SQLite只有简单的五种基本类型：NULL、INTEGER、REAL、TEXT、BLOB。并没有像其他数据库那样为时间、日期设立特定的存储类型，对于这点，官方文档只做了如下简短的解释：
    "SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

   Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions."
   其实，简洁就代表着简单，我们只需掌握到SQLite用TEXT存储字符串格式的日期时间，用INTEGER存储时间戳就可以了，Julian类型可适当掌握。自然，对日期时间的操作需要通过函数来完成的，SQLite提供了一下五种函数。

1. date(timestring, modifier, modifier, ...)
2. time(timestring, modifier, modifier, ...)
3. datetime(timestring, modifier, modifier, ...)
4. julianday(timestring, modifier, modifier, ...)
5. strftime(format, timestring, modifier, modifier, ...)

  其中，strtime最为强大，其它四种函数都能通过它等效完成。仅仅通过这简单的五个函数，我们就可以完成对日期时间从timestring到提么stamp的相互转化以及相应的加减计算。重点要掌握timestring和modifier的格式，熟练之后，对于时间日期的操作应该是非常快的。总体来说，SQLite对时间日期的操作非常简洁轻便，也是身为数据库小王子的沂濛最喜欢的一类数据库。
   关于函数的具体说明，请参考我给出的官方说明文档链接。

  个人觉得，Mysql提供的日期时间类型和函数是最丰富多彩，最完美的。在Mysql中，可以存储日期、时间的类型有很多种：date、time、datetime、timestamp、year等等，都是独立的数据类型，要理解它们之间的区别和联系。而对于函数，可以说是更加繁复。发现一个有意思的现象，Mysql中很多对于日期时间的操作都可以不止用一种方法来完成，如果细心，你总会找到其他有趣的函数来等效完成。因为对Mysql实在太过熟悉，不在详述，只列举一下常用的函数及用法，方便参考。

• DAYOFWEEK(date) ——返回日期date是星期几(1=星期天,2=星期一,……7=星期六,ODBC标准)WEEKDAY(date) ——返回日期date是星期几(0=星期一,1=星期二,……6= 星期天)。
• DAYOFMONTH(date) ——返回date是一月中的第几日(在1到31范围内)
• DAYOFYEAR(date) ——返回date是一年中的第几日(在1到366范围内) 
• MONTH(date) ——返回date中的月份数值
• DAYNAME(date) ——返回date是星期几(按英文名返回)
• MONTHNAME(date) ——返回date是几月(按英文名返回)
• QUARTER(date) ——返回date是一年的第几个季度
•  WEEK(date,first)——返回date是一年的第几周(first默认值0,first取值1表示周一是周的开始,0从周日开始)YEAR(date) ——返回date的年份(范围在1000到9999)
•  HOUR(time) ——返回time的小时数(范围是0到23)
• MINUTE(time) ——返回time的分钟数(范围是0到59)
• SECOND(time) ——返回time的秒数(范围是0到59)
• PERIOD_ADD(P,N) ——增加N个月到时期P并返回(P的格式YYMM或YYYYMM)
• PERIOD_DIFF(P1,P2) ——返回在时期P1和P2之间月数(P1和P2的格式YYMM或YYYYMM)DATE_ADD(date,INTERVAL expr type)、ADDDATE(date,INTERVAL expr type)——对日期时间进行加法运算
• DATE_SUB(date,INTERVAL expr type)、SUBDATE(date,INTERVAL expr type)——对日期时间进行减法运算
• TO_DAYS(date) ——返回日期date是西元0年至今多少天(1582年以后)
• FROM_DAYS(N) ——给出西元0年至今多少天返回DATE值(1582年以后)
• DATE_FORMAT(date,format)、TIME_FORMAT(time,format) ——根据format字符串格式化date、time值
• CURDATE() 、CURRENT_DATE()——以'YYYY-MM-DD'或YYYYMMDD格式返回当前日期值(根据返回值所处上下文是字符串或数字)
• CURTIME() 、CURRENT_TIME()——以'HH:MM:SS'或HHMMSS格式返回当前时间值(根据返回值所处上下文是字符串或数字)
• NOW() 、SYSDATE() 、CURRENT_TIMESTAMP()——以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回当前日期时间(根据返回值所处上下文是字符串或数字)
• UNIX_TIMESTAMP(date) ——返回一个Unix时间戳(从'1970-01-01 00:00:00'GMT开始的秒数,date默认值为当前时间)
• FROM_UNIXTIME(unix_timestamp,format) ——以format字符串格式返回时间戳的值
• SEC_TO_TIME(seconds) ——以'HH:MM:SS'或HHMMSS格式返回秒数转成的TIME值(根据返回值所处上下文是字符串或数字)
• TIME_TO_SEC(time) ——返回time值有多少秒

  Oracle作为稳定的商业数据库，对日期时间的操作也十分严谨。不同于其他数据库，Oracle的日期时间类型只有一种date，相当于Mysql里的datetime。Oracle提供的函数同样丰富，个人经验，对于Oracle，有两个函数是一定要熟练掌握的，它们分别是to_date()和to_char()，两个函数提供了大量参数和modifier，对它们进行嵌套使用，可以完成许多强大的功能，复杂的操作。
   可以这么说，Oracle对date的操作就是对to_date()和to_char()的使用。在网上找了一些to_date()和to_char()的官方用例，感兴趣的朋友可以体会一下，看一下链接。
   Oracle的函数使用和Mysql类似，只是细节不同，这里不再赘述。
    ......
   最后，总结一下，数据库操作中对日期时间的操作不是难点，但是不同的数据库的语法各异，所以如要熟练掌握某个数据库，还是要对这个数据库对日期时间的操作存储了如指掌，这样才会提高效率，达到值得飞跃。