Learn about MySQL's date-related functions

MySQL Gets the current date time function

Get the current date + time (date + times) function: Now ()

Mysql> Select Now (); +---------------------+| Now () |+---------------------+| 2008-08-08 22:20:46 |+---------------------+

Get the current date + time (date + times) function: Sysdate ()
The Sysdate () date-time function is similar to now (), except that it is obtained at the execution start value, and sysdate () dynamically gets the value when the function executes. See the following example to understand:

Mysql> Select Now (), Sleep (3), now (), +---------------------+----------+---------------------+| Now () | Sleep (3) | Now () |+---------------------+----------+---------------------+| 2008-08-08 22:28:21 | 0 | 2008-08-08 22:28:21 |+---------------------+----------+---------------------+

Sysdate () Date-time functions, which are rarely used under normal circumstances.

MySQL Gets the current timestamp function: 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 |+---------------------+---------------------+

MySQL Date conversion function, time conversion function

MySQL Date/time to STR (date/time converted to string) function: Date_format (Date,format), Time_format (Time,format)

Mysql> Select Date_format (' 2008-08-08 22:23:01 ', '%y%m%d%h%i%s '); +--------------------------------------------- -------+| Date_format (' 2008-08-08 22:23:01 ', '%y%m%d%h%i%s ') |+----------------------------------------------------+| 20080808222301 |+----------------------------------------------------+

MySQL date, time conversion function: Date_format (Date,format), Time_format (Time,format) is able to convert a date/time into a variety of string formats. It is an inverse conversion of the str_to_date (Str,format) function.

MySQL str to date (string converted to date) function: Str_to_date (str, format)

Select Str_to_date (' 08/09/2008 ', '%m/%d/%y '); --2008-08-09select str_to_date (' 08/09/08 ', '%m/%d/%y '); --2008-08-09select str_to_date (' 08.09.2008 ', '%m.%d.%y '); --2008-08-09select str_to_date (' 08:09:30 ', '%h:%i:%s '); --08:09:30select str_to_date (' 08.09.2008 08:09:30 ', '%m.%d.%y%h:%i:%s '); --2008-08-09 08:09:30

As you can see, the Str_to_date (Str,format) conversion function allows you to convert some disorganized strings into a date format. In addition, it can also be converted to time. "Format" can be see the MySQL manual.

MySQL (date, day) conversion function: To_days (dates), from_days (days)

Select To_days (' 0000-00-00 '); --0select to_days (' 2008-08-08 '); --733627

MySQL (time, seconds) conversion function: Time_to_sec (times), sec_to_time (seconds)

Select Time_to_sec (' 01:00:05 '); --3605select Sec_to_time (3605); --' 01:00:05 '

MySQL Patchwork Date, time function: 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 (Unix timestamp, date) conversion function

Unix_timestamp (), Unix_timestamp (date), From_unixtime (Unix_timestamp), From_unixtime (Unix_timestamp,format)

Here's an example:

Select Unix_timestamp (); --1218290027select Unix_timestamp (' 2008-08-08 '); --1218124800select Unix_timestamp (' 2008-08-08 12:30:00 '); --1218169800select 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 '

MySQL Date Time calculation function

MySQL adds a time interval for the date: Date_add ()

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 yea r); Select Date_add (@dt, Interval-1 Day); --SUB 1 day

MySQL adddate (), Addtime () function, can be replaced with Date_add (). The following is an example of the date_add () implementation of the Addtime () feature:

mysql> Set @dt = ' 2008-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) |+------------------------------------------------+| 2008-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) |+-------------------------------------------------+| 2008-08-10 13:28:03 |+-------------------------------------------------+

MySQL for date minus one time interval: Date_sub ()

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 date_sub () datetime functions and Date_add () are used in a consistent, no-repeat.

MySQL date, Time subtraction function: DateDiff (DATE1,DATE2), Timediff (time1,time2)

MySQL DateDiff (DATE1,DATE2): Two date subtraction Date1-date2, returns the number of days. Select DateDiff (' 2008-08-08 ', ' 2008-08-01 '); --7select DateDiff (' 2008-08-01 ', ' 2008-08-08 '); ---7

MySQL 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:08select Timediff (' 08:08:08 ', ' 00:00:00 '); -08:08:08

Note: the Timediff (time1,time2) function must have the same two parameter types.

MySQL Timestamp (timestamp) conversion, increment, decrement function:

Timestamp (date)--date to Timestamptimestamp (dt,time)--DT + timetimestampadd (unit,interval,datetime_expr)-- Timestampdiff (UNIT,DATETIME_EXPR1,DATETIME_EXPR2)--

Take a look at the Example section:

Select timestamp (' 2008-08-08 '); --2008-08-08 00:00:00select timestamp (' 2008-08-08 08:00:00 ', ' 01:01:01 '); --2008-08-08 09:01:01select timestamp (' 2008-08-08 08:00:00 ', ' 10 01:01:01 '); --2008-08-18 09:01:01select Timestampadd (day, 1, ' 2008-08-08 08:00:00 '); --2008-08-09 08:00:00select date_add (' 2008-08-08 08:00:00 ', Interval 1 day); --2008-08-09 08:00:00mysql Timestampadd () function is similar to Date_add (). Select Timestampdiff (year, ' 2002-05-01 ', ' 2001-01-01 '); ---1select timestampdiff (day, ' 2002-05-01 ', ' 2001-01-01 '); ---485select Timestampdiff (hour, ' 2008-08-08 12:00:00 ', ' 2008-08-08 00:00:00 '); ---12select datediff (' 2008-08-08 12:00:00 ', ' 2008-08-01 00:00:00 '); --7

The MySQL Timestampdiff () function is much stronger than the DateDiff () function, and DateDiff () can only calculate the number of days between two dates (date).

MySQL time Zone (timezone) conversion function

Convert_tz (Dt,from_tz,to_tz) Select Convert_tz (' 2008-08-08 12:00:00 ', ' +08:00 ', ' +00:00 '); --2008-08-08 04:00:00

Time zone conversions can also be achieved by Date_add, Date_sub, Timestampadd.

Select Date_add (' 2008-08-08 12:00:00 ', Interval-8 hour); --2008-08-08 04:00:00select date_sub (' 2008-08-08 12:00:00 ', Interval 8 hour); --2008-08-08 04:00:00select timestampadd (Hour,-8, ' 2008-08-08 12:00:00 '); --2008-08-08 04:00:00

Learn about MySQL date correlation function (GO)