经典的MySQL时间和日期加减运算语句

------------------+ mysql> select time_format('22:23:01', '%H.%i.%s'); +-------------------------------------+| time_format('22:23:01', '%H.%i.%s') |+-------------------------------------+| 22.23.01 |+-------------------------------------+ MySQL 日期、时间转换函数：date_format(date,format), time_format(time,format) 能够把一个日期/时间转换成各种各样的字符串格式。它是 str_to_date(str,format) 函数的 一个逆转换。5. MySQL 获得国家地区时间格式函数：get_format()MySQL get_format() 语法：get_format(date|time|datetime, 'eur'|'usa'|'jis'|'iso'|'internal' MySQL get_format() 用法的全部示例：select get_format(date,'usa') ; -- '%m.%d.%Y'select get_format(date,'jis') ; -- '%Y-%m-%d'select get_format(date,'iso') ; -- '%Y-%m-%d'select get_format(date,'eur') ; -- '%d.%m.%Y'select get_format(date,'internal') ; -- '%Y%m%d'select get_format(datetime,'usa') ; -- '%Y-%m-%d %H.%i.%s'select get_format(datetime,'jis') ; -- '%Y-%m-%d %H:%i:%s'select get_format(datetime,'iso') ; -- '%Y-%m-%d %H:%i:%s'select get_format(datetime,'eur') ; -- '%Y-%m-%d %H.%i.%s'select get_format(datetime,'internal') ; -- '%Y%m%d%H%i%s'select get_format(time,'usa') ; -- '%h:%i:%s %p'select get_format(time,'jis') ; -- '%H:%i:%s'select get_format(time,'iso') ; -- '%H:%i:%s'select get_format(time,'eur') ; -- '%H.%i.%s'select get_format(time,'internal') ; -- '%H%i%s' MySQL get_format() 函数在实际中用到机会的比较少。6. MySQL 拼凑日期、时间函数：makdedate(year,dayofyear), maketime(hour,minute,second)select makedate(2001,31); -- '2001-01-31'select makedate(2001,32); -- '2001-02-01' select maketime(12,15,30); -- '12:15:30' 五、MySQL 时间戳（Timestamp）函数1. MySQL 获得当前时间戳函数：current_timestamp, current_timestamp()mysql> select current_timestamp, current_timestamp(); +---------------------+---------------------+| current_timestamp | current_timestamp() |+---------------------+---------------------+| 2008-08-09 23:22:24 | 2008-08-09 23:22:24 |+---------------------+---------------------+ 2. MySQL （Unix 时间戳、日期）转换函数：unix_timestamp(),unix_timestamp(date),from_unixtime(unix_timestamp),from_unixtime(unix_timestamp,format) 下面是示例：select unix_timestamp(); -- 1218290027select unix_timestamp('2008-08-08'); -- 1218124800select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800 select from_unixtime(1218290027); -- '2008-08-09 21:53:47'select from_unixtime(1218124800); -- '2008-08-08 00:00:00'select from_unixtime(1218169800); -- '2008-08-08 12:30:00' select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August 12:30:00 2008'