MySQL Date function

#获得当前日期 + time (date) function
Select Now ()

#获得当前时间戳函数
Select Current_timestamp, Current_timestamp ()


#Date/time to STR (date/time converted to string) function
Select Date_format (' 2008-08-08 22:23:01 ', '%y%m%d%h%i%s ')


#字符串转换为日期函数
Select Str_to_date (' 08/09/2008 ', '%m/%d/%y '); -2008-08-09
Select Str_to_date (' 08/09/08 ', '%m/%d/%y '); -2008-08-09
Select Str_to_date (' 08.09.2008 ', '%m.%d.%y '); -2008-08-09
Select Str_to_date (' 08:09:30 ', '%h:%i:%s '); -08:09:30
Select Str_to_date (' 08.09.2008 08:09:30 ', '%m.%d.%y%h:%i:%s '); --2008-08-09 08:09:30


# (date, days) conversion function
Select To_days (' 0000-00-00 '); --0
Select To_days (' 2008-08-08 '); --733627


# (time, seconds) conversion function
Select Time_to_sec (' 01:00:05 '); --3605
Select Sec_to_time (3605); --' 01:00:05 '

#拼凑日期, Time function
Select Makedate (2001,31); --' 2001-01-31 '
Select Makedate (2001,32); --' 2001-02-01 '
Select Maketime (12,15,30); --' 12:15:30 '


# (Unix timestamp, date) conversion function
Select Unix_timestamp (); --1218290027
Select Unix_timestamp (' 2008-08-08 '); --1218124800
Select 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 '); --' 8th August 12:30:00 2008 '


#日期, Time subtraction function
DateDiff (DATE1,DATE2): Two date subtraction Date1-date2, returns the number of days.
Select DateDiff (' 2008-08-08 ', ' 2008-08-01 '); --7
Select DateDiff (' 2008-08-01 ', ' 2008-08-08 '); ---7


#timediff (time1,time2): Two date subtraction time1-time2, returns the time difference value.
Select Timediff (' 2008-08-08 08:08:08 ', ' 2008-08-08 00:00:00 '); -08:08:08
Select Timediff (' 08:08:08 ', ' 00:00:00 '); -08:08:08


#指定日期1秒后 interval
SELECT date_add (' 2010-12-31 23:59:59 ', INTERVAL 1 SECOND)


#指定日期1天后:
SELECT date_add (' 2010-12-31 23:59:59 ', INTERVAL 1 day);



#定日期减去10小时:
SELECT date_add (' 2011-01-01 00:00:00 ', INTERVAL '-1 ' day_hour);


#指定日期的一个月前
SELECT date_sub (' 2011-01-02 ', INTERVAL Day);


#指定日期的前一天:
SELECT date_add (' 2011-01-01 ', INTERVAL-1 Day);

MySQL Date function