Application of time and date function in MySQL

First, MySQL gets the current date time function

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

Mysql> Select Now (); +---------------------+| Now ()               |+---------------------+| 2015-01-22 22:20:46 |+---------------------+

In addition to the now () function to get the current datetime, MySQL has the following function:

Current_timestamp (), Current_timestamp,localtime (), Localtime,localtimestamp    --(v4.0.6), Localtimestamp ()  --(v4.0.6)

These date-time functions are equivalent to now (). Since the now () function is short and easy to remember, it is recommended that you always use now () instead of the functions listed above.

1.2 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 ()               |+---------------------+----------+---------------------+| 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 |+---------------------+----------+---------------------+

As you can see, the time value of the now () function two times is the same, although sleep is 3 seconds in the middle, and the time value of the sysdate () function is 3 seconds apart from two. In MySQL Manual, this describes Sysdate (): Return the time at which the function executes.

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

2. Get the current date (date) function: Curdate ()

Mysql> select Curdate (); +------------+| Curdate ()  |+------------+| 2015-01-22 |+------------+

Where the following two date functions are equivalent to Curdate ():

Current_date ()
, current_date

3. Get the current time function: Curtime ()

Mysql> select Curtime (); +-----------+| Curtime () |+-----------+| 22:41:30  |+-----------+

Where the following two time functions are equivalent to Curtime ():

Current_time ()
, Current_time

4. Get the current UTC datetime function: Utc_date (), Utc_time (), Utc_timestamp ()

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 |+---------------------+------------+------------+---------------------+

Because our country is located in the East Eight time zone, local time = UTC time + 8 hour. UTC time is useful when the business involves multiple countries and regions.
Second, MySQL date time Extract (select) function.

1. Select each part of the DateTime: Date, time, year, quarter, month, day, hour, minute, second, microsecond

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 () function, you can implement similar functions above:

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

The MySQL Extract () function has no function other than date (), Time (), and all other functions one should. And also has the choice ' day_microsecond ' and so on function. Note that this is not just the day and the microsecond, but the part of the date that has been selected to the Microsecond section. It's tough enough!

The only downside to the MySQL Extract () function is that you need to hit the keyboard a few more times.

3. MySQL dayof ... Function: DayOfWeek (), DayOfMonth (), DayOfYear ()

Returns the position of the date parameter, in one week, month, year, respectively.

Set @dt = ' 2015-01-22 ';

Select DayOfWeek (@dt); --6
Select DayOfMonth (@dt); --8
Select DayOfYear (@dt); --221

The date ' 2015-01-22′ is the 6th day of the week (1 = Sunday, 2 = Monday, ..., 7 = Saturday), the 8th day of January, and the No. 221 day of the year.

4. MySQL Week ... Functions: Week (), WeekOfYear (), DayOfWeek (), Weekday (), Yearweek ()

Set @dt = ' 2015-01-22 ';

Select Week (@dt); --31
Select Week (@dt, 3); --32
Select WeekOfYear (@dt); --32

Select DayOfWeek (@dt); --6
Select Weekday (@dt); --4

Select Yearweek (@dt); --201431

MySQL week () function, can have two parameters, specific to see the manual. WeekOfYear () and week () are calculated as "one day" in the first week of the year. WeekOfYear (@dt) is equivalent to week (@dt, 3).

The MySQL Weekday () function, like DayOfWeek (), returns the position of "one day" in a week. The difference is in reference to the standard, weekday: (0 = Monday, 1 = Tuesday, ..., 6 = Sunday); DayOfWeek: (1 = Sunday, 2 = Monday, ..., 7 = Saturday)

MySQL Yearweek () function, returns year + week position (31).

5. MySQL returns the week and month name functions: Dayname (), MonthName ()

Set @dt = ' 2015-01-22 ';

Select Dayname (@dt); --Friday
Select MonthName (@dt); --August

Think, how to return the name of Chinese?

6. MySQL Last_day () function: Returns the last day of the month.

Select Last_day (' 2014-02-01 '); -2014-02-29
Select Last_day (' 2015-01-22 '); -2014-08-31

The MySQL Last_day () function is useful, for example, I want to get the number of days in the current month, which can be calculated as follows:

Mysql> Select Now (), Day (Last_day (now)) as days;+---------------------+------+| Now ()               | days |+---------------------+------+| 2014-08-09 11:45:45 |   |+---------------------+------+

Third, MySQL date time calculation function

1. 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 wee k); Select Date_add (@dt, Interval 1 month), select Date_add (@dt, Interval 1 quarter), select Date_add (@dt, interval 1 year); s       Elect Date_add (@dt, Interval-1 Day); --SUB 1 Daymysql adddate (), Addtime () function, can be substituted with Date_add (). The following is an example of Date_add () implementing Addtime () function:mysql> Set @dt = ' 2014-08-09 12:12:33 ';mysql>mysql> select Date_add (@dt, Interva L ' 01:15:30 ' Hour_second); +------------------------------------------------+| Date_add (@dt, Interval ' 01:15:30 ' Hour_second) |+------------------------------------------------+| 2014-08-09 13:28:03 |+------------------------------------------------+mysql> Select Date_ad D (@dt, Interval ' 1 01:15:30 ' day_second); +-------------------------------------------------+| Date_add (@dt, Interval ' 1 01:15:30 ' Day_second) |+-------------------------------------------------+| 2014-08-10 13:28:03 |+-------------------------------------------------+

The Date_add () function adds "1 hours, 15 minutes, 30 seconds" and "1 days, 1 hours, 15 minutes, 30 seconds" for @dt respectively. Recommendation: Always use the Date_add () date-time function instead of adddate (), Addtime ().

2. 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. In addition, MySQL also has two functions subdate (), Subtime (), recommended, with Date_sub () to replace.

3. MySQL Alternative Date function: Period_add (p,n), Period_diff (P1,P2)

The format of the function parameter "P" is "yyyymm" or "yymm", and the second parameter "n" means increment or subtract N month (month).

MySQL Period_add (p,n): Date plus/minus N months.

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): Date p1-p2, return N months. Mysql> Select Period_diff (201408, 201401); +-----------------------------+| Period_diff (201408, 201401) |+-----------------------------+|                           7 |+-----------------------------+

In MySQL, these two date functions are rarely used under normal circumstances.

4. 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 (' 2015-01-22 ', ' 2014-08-01 ');  --7select DateDiff (' 2014-08-01 ', ' 2015-01-22 ');  ---7MySQL Timediff (time1,time2): Two date subtraction time1-time2, returns the time difference value. 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

Note: the Timediff (time1,time2) function must have the same two parameter types.
Four, MySQL date conversion function, time conversion function

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

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

2. MySQL (date, day) conversion functions: to_days (date), From_days (days)

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 '

3. MySQL str to date (string conversion to date) function: Str_to_date (str, format)

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

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.

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

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 |+------------------------------------------------+mysql> Select Date_fo Rmat (' 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 |+-------------------------------------+ 

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.

5. MySQL gets the national region time Format function: Get_format ()

MySQL Get_format () syntax:

Get_format (date|time|datetime, ' EUR | USA ' | ' JIS ' | ' ISO ' | ' All examples of internal '

MySQL get_format () Usage:

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 ' 

The MySQL Get_format () function uses less opportunity in practice.

6. MySQL Patchwork Date, time function: Makdedate (year,dayofyear), Maketime (Hour,minute,second)

Select Makedate (2011,31); --' 2011-01-31 '
Select Makedate (2011,32); --' 2011-02-01 '

Select Maketime (12,15,30); --' 12:15:30 '

V. MySQL timestamp (Timestamp) function

1. MySQL Gets the current timestamp function: Current_timestamp, Current_timestamp ()

Mysql> Select Current_timestamp, Current_timestamp (), +---------------------+---------------------+| Current_timestamp   | current_timestamp () |+---------------------+---------------------+| 2014-08-09 23:22:24 | 2014-08-09 23:22:24 |+---------------------+---------------------+

2. 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 (); --1218290027
Select Unix_timestamp (' 2015-01-22 '); --1218124800
Select Unix_timestamp (' 2015-01-22 12:30:00 '); --1218169800

Select From_unixtime (1218290027); --' 2014-08-09 21:53:47 '
Select From_unixtime (1218124800); --' 2015-01-22 00:00:00 '
Select From_unixtime (1218169800); --' 2015-01-22 12:30:00 '

Select From_unixtime (1218169800, '%Y%d%M%h:%i:%s%x '); --' 8th August 12:30:00 2014 '

3. MySQL timestamp (timestamp) conversion, increment, decrement function:

Timestamp (date)--date to timestamp
Timestamp (dt,time)--DT + Time
Timestampadd (unit,interval,datetime_expr)--
Timestampdiff (UNIT,DATETIME_EXPR1,DATETIME_EXPR2)--

Take a look at the Example section:

Select timestamp (' 2015-01-22 '); --2015-01-22 00:00:00
Select Timestamp (' 2015-01-22 08:00:00 ', ' 01:01:01 '); --2015-01-22 09:01:01
Select Timestamp (' 2015-01-22 08:00:00 ', ' 10 01:01:01 '); --2014-08-18 09:01:01

Select Timestampadd (Day, 1, ' 2015-01-22 08:00:00 '); --2015-01-09 08:00:00
Select Date_add (' 2015-01-22 08:00:00 ', Interval 1 day); --2015-01-09 08:00:00

The MySQL Timestampadd () function is similar to Date_add ().

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

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

Convert_tz (Dt,from_tz,to_tz)

Select Convert_tz (' 2015-01-22-12:00:00 ', ' +08:00 ', ' +00:00 '); --2015-01-22 04:00:00
Time zone conversions can also be achieved by Date_add, Date_sub, Timestampadd.

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

Reference: http://www.cnblogs.com/redfox241/archive/2009/07/23/1529092.html

Application of time and date function in MySQL