一、MySQL 获得当前日期时间 函数
1.1 获得当前日期+时间(date + time)函数:now()mysql> select now();+---------------------+| now() |+---------------------+| 2015-01-22 22:20:46 |+---------------------+
current_timestamp(),current_timestamp,localtime(),localtime,localtimestamp -- (v4.0.6),localtimestamp() -- (v4.0.6)
mysql> select now(), sleep(3), now();+---------------------+----------+---------------------+| now() | sleep(3) | now() |+---------------------+----------+---------------------+| 2015-01-22 22:28:21 | 0 | 2015-01-22 22:28:21 |+---------------------+----------+---------------------+mysql> select sysdate(), sleep(3), sysdate();+---------------------+----------+---------------------+| sysdate() | sleep(3) | sysdate() |+---------------------+----------+---------------------+| 2015-01-22 22:28:41 | 0 | 2015-01-22 22:28:44 |+---------------------+----------+---------------------+
mysql> select curdate();+------------+| curdate() |+------------+| 2015-01-22 |+------------+
mysql> select curtime();+-----------+| curtime() |+-----------+| 22:41:30 |+-----------+
mysql> select utc_timestamp(), utc_date(), utc_time(), now()+---------------------+------------+------------+---------------------+| utc_timestamp() | utc_date() | utc_time() | now() |+---------------------+------------+------------+---------------------+| 2015-01-22 14:47:11 | 2015-01-22 | 14:47:11 | 2015-01-22 22:47:11 |+---------------------+------------+------------+---------------------+
set @dt = '2014-09-10 07:15:30.123456';select date(@dt); -- 2014-09-10select time(@dt); -- 07:15:30.123456select year(@dt); -- 2014select quarter(@dt); -- 3select month(@dt); -- 9select week(@dt); -- 36select day(@dt); -- 10select hour(@dt); -- 7select minute(@dt); -- 15select second(@dt); -- 30select microsecond(@dt); -- 123456
2. MySQL Extract() 函数,可以上面实现类似的功能:
set @dt = '2014-09-10 07:15:30.123456';select extract(year from @dt); -- 2014select extract(quarter from @dt); -- 3select extract(month from @dt); -- 9select extract(week from @dt); -- 36select extract(day from @dt); -- 10select extract(hour from @dt); -- 7select extract(minute from @dt); -- 15select extract(second from @dt); -- 30select extract(microsecond from @dt); -- 123456select extract(year_month from @dt); -- 201409select extract(day_hour from @dt); -- 1007select extract(day_minute from @dt); -- 100715select extract(day_second from @dt); -- 10071530select extract(day_microsecond from @dt); -- 10071530123456select extract(hour_minute from @dt); -- 715select extract(hour_second from @dt); -- 71530select extract(hour_microsecond from @dt); -- 71530123456select extract(minute_second from @dt); -- 1530select extract(minute_microsecond from @dt); -- 1530123456select extract(second_microsecond from @dt); -- 30123456
mysql> select now(), day(last_day(now())) as days;+---------------------+------+| now() | days |+---------------------+------+| 2014-08-09 11:45:45 | 31 |+---------------------+------+
set @dt = now();select date_add(@dt, interval 1 day); -- add 1 dayselect date_add(@dt, interval 1 hour); -- add 1 hourselect date_add(@dt, interval 1 minute); --select date_add(@dt, interval 1 second);select date_add(@dt, interval 1 microsecond);select date_add(@dt, interval 1 week);select date_add(@dt, interval 1 month);select date_add(@dt, interval 1 quarter);select date_add(@dt, interval 1 year);select date_add(@dt, interval -1 day); -- sub 1 dayMySQL adddate(), addtime()函数,可以用 date_add() 来替代。下面是 date_add() 实现 addtime() 功能示例:mysql> set @dt = '2014-08-09 12:12:33';mysql>mysql> select date_add(@dt, interval '01:15:30' hour_second);+------------------------------------------------+| date_add(@dt, interval '01:15:30' hour_second) |+------------------------------------------------+| 2014-08-09 13:28:03 |+------------------------------------------------+mysql> select date_add(@dt, interval '1 01:15:30' day_second);+-------------------------------------------------+| date_add(@dt, interval '1 01:15:30' day_second) |+-------------------------------------------------+| 2014-08-10 13:28:03 |+-------------------------------------------------+
mysql> select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second);+----------------------------------------------------------------+| date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second) |+----------------------------------------------------------------+| 1997-12-30 22:58:59 |+----------------------------------------------------------------+
mysql> select period_add(201408,2), period_add(20140808,-2)+----------------------+-------------------------+| period_add(201408,2) | period_add(20140808,-2) |+----------------------+-------------------------+| 201410 | 20140806 |+----------------------+-------------------------+MySQL period_diff(P1,P2):日期 P1-P2,返回 N 个月。mysql> select period_diff(201408, 201401);+-----------------------------+| period_diff(201408, 201401) |+-----------------------------+| 7 |+-----------------------------+
MySQL datediff(date1,date2):两个日期相减 date1 - date2,返回天数。select datediff('2015-01-22', '2014-08-01'); -- 7select datediff('2014-08-01', '2015-01-22'); -- -7MySQL timediff(time1,time2):两个日期相减 time1 - time2,返回 time 差值。select timediff('2015-01-22 08:08:08', '2015-01-22 00:00:00'); -- 08:08:08select timediff('08:08:08', '00:00:00'); -- 08:08:08
select to_days('0000-00-00'); -- 0select to_days('2015-01-22'); -- 733627select from_days(0); -- '0000-00-00'select from_days(733627); -- '2015-01-22'
select str_to_date('08/09/2014', '%m/%d/%Y'); -- 2014-08-09select str_to_date('08/09/08' , '%m/%d/%y'); -- 2014-08-09select str_to_date('08.09.2014', '%m.%d.%Y'); -- 2014-08-09select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30select str_to_date('08.09.2014 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2014-08-09 08:09:30
mysql> select date_format('2015-01-22 22:23:00', '%W %M %Y');+------------------------------------------------+| date_format('2015-01-22 22:23:00', '%W %M %Y') |+------------------------------------------------+| Friday August 2014 |+------------------------------------------------+mysql> select date_format('2015-01-22 22:23:01', '%Y%m%d%H%i%s');+----------------------------------------------------+| date_format('2015-01-22 22:23:01', '%Y%m%d%H%i%s') |+----------------------------------------------------+| 20140808222301 |+----------------------------------------------------+mysql> select time_format('22:23:01', '%H.%i.%s');+-------------------------------------+| time_format('22:23:01', '%H.%i.%s') |+-------------------------------------+| 22.23.01 |+-------------------------------------+
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> select current_timestamp, current_timestamp();+---------------------+---------------------+| current_timestamp | current_timestamp() |+---------------------+---------------------+| 2014-08-09 23:22:24 | 2014-08-09 23:22:24 |+---------------------+---------------------+
select timestampdiff(year,'2012-05-01','2011-01-01'); -- -1select timestampdiff(day ,'2012-05-01','2011-01-01'); -- -485select timestampdiff(hour,'2015-01-22 12:00:00','2015-01-22 00:00:00'); -- -12select datediff('2015-01-22 12:00:00', '2014-08-01 00:00:00'); -- 7
select date_add('2015-01-22 12:00:00', interval -8 hour); -- 2015-01-22 04:00:00select date_sub('2015-01-22 12:00:00', interval 8 hour); -- 2015-01-22 04:00:00select timestampadd(hour, -8, '2015-01-22 12:00:00'); -- 2015-01-22 04:00:00