MySQL Date function

Write in front

MySQL learning, intermittent, today continue to learn the date of MySQL operation.

Series Articles

MySQL CREATE database, create data table

MySQL's select,insert,delete,update

MySQL group By,order by

MySQL's Count,max,min,sum,avg,celing,floor

MySQL Combat

1. Get the current time format Yyyy-mm-dd HH:MM:SS

Select Now ();

Results

Another function that gets the current time is: Sysdate ();

The difference: Now () is obtained at the execution start value, and sysdate () dynamically gets the value when the function executes.

Select sysdate (), Sleep (3), now ();

In practice, however, the sysdate () function is seldom used.

2, the time stamp function can also get the current time

Select Current_timestamp (),current_timestamp;

3, get the date, month day seconds

SelectDate'2015-06-27 08:33:32'), Year('2015-06-27 08:33:32'),Month('2015-06-27 08:33:32'), Day('2015-06-27 08:33:32'), Time ('2015-06-27 08:33:32'), second ('2015-06-27 08:33:32');

4. Date formatting function

Date_format (Date,format), Time_format (Time,format)

Select date_format ('2015-08-08 22:23:01'%y%m%d%h%i%s' );

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.

Select str_to_date ('08/08/2015','%m/%d/%y');

Select str_to_date ('08/08/15','%m/%d/%y ' );

Select str_to_date ('08.08.15','%m.%d.%y');

Select str_to_date ('08:09:30','%h:%i:%s ' );

Select str_to_date ('08.09.2015 08:09:30'%m.%d.%y%h:%i:%s '

5. (date, day) conversion function: To_days (date), From_days (days)

Select to_days ('2015-06-25'

Select from_days ('736139');

Of course, there are operations on the date, the number of days, and also the operation of the year, month, day, time, minute, and second, which await you to discover.

Select time_to_sec ('01:00:05'--  3605Select sec_to_ Time (3605--  ' 01:00:05 ' )

Patchwork date, Time function: Makdedate (year,dayofyear), Maketime (Hour,minute,second)

Select makedate (2001,+--  ' 2001-01-31 'select makedate (  2001,+--  ' 2001-02-01 'select maketime (  - -- ' 12:15:30 '

(Unix timestamp, date) conversion function

SelectUnix_timestamp ();--1435371899SelectUnix_timestamp ('2015-08-08 12:30:00');--' 1439008200 'SelectFrom_unixtime ('1435371899');SelectFrom_unixtime (1218124800);--' 2015-06-27 10:24:59.000000 'SelectFrom_unixtime ('1439008200');--' 2015-08-08 12:30:00.000000 'SelectFrom_unixtime (1435371899,'%Y%d%M%h:%i:%s%x');--' 27th June 10:24:59 '

6. Date-Time calculation function

Set @dt=Now ();SelectDate_add (@dt, Interval1  Day);--' 2015-06-28 10:30:38 ' plus one daySelectDate_add (@dt, Interval1hour);--' 2015-06-27 11:32:10 ' plus an hourSelectDate_add (@dt, Interval1minute);--' 2015-06-27 10:33:33 'SelectDate_add (@dt, Interval1second);--' 2015-06-27 10:33:34 'SelectDate_add (@dt, Interval1microsecond);SelectDate_add (@dt, Interval1week);SelectDate_add (@dt, Interval1 Month);SelectDate_add (@dt, Interval1quarter);SelectDate_add (@dt, Interval1  Year);SelectDate_add (@dt, Interval-1  Day); minus one day

Adddate (), Addtime () function, can be substituted with Date_add ().

Set @dt = ' 2015-08-09 12:12:33 ' ; Select date_add (@dt'01:15:30' hour_second);

From the results, it is easier to understand, in hours and seconds plus the hours and seconds you set for the time.

Set @dt = ' 2015-08-09 12:12:33 ' ; Select date_add (@dt'2 01:15:30' day_second);

Minus one time

Set @dt = ' 2015-08-09 12:12:33 ' ; Select date_sub (@dt'2 01:15:30' day_second);

Date, time subtraction function: DateDiff (DATE1,DATE2), Timediff (time1,time2)

 set   @dt  =    2015-08-09 12:12:33     set   @now  =  now ();  select  datediff   ( @now ,  @dt ); --  

Set @dt = ' 2015-08-09 12:12:33 ' ; Set @now = Now (); Select Timediff (@now,@dt);

Note: the Timediff (time1,time2) function must have the same two parameter types, either Yyyy-mm-dd HH:mm:ss or HH:MM:SS.

Timestamp (timestamp) conversion, increment, decrement function:

timestamp -- Date to timestamp timestamp -- DT + Time

Select timestamp('2015-08-08');--2015-08-08 00:00:00Select timestamp('2015-08-08 08:00:00','01:01:01');--2015-08-08 09:01:01Select timestamp('2015-08-08 08:00:00','Ten 01:01:01');--2015-08-18 09:01:01SelectTimestampadd ( Day,1,'2015-08-08 08:00:00');--2015-08-09 08:00:00SelectDate_add ('2015-08-08 08:00:00', Interval1  Day);--2015-08-09 08:00:00SelectTimestampdiff ( Year,'2002-05-01','2001-01-01');---1SelectTimestampdiff ( Day,'2002-05-01','2001-01-01');---485SelectTimestampdiff (Hour,'2008-08-08 12:00:00','2008-08-08 00:00:00');---12Select DateDiff('2015-08-08 12:00:00','2015-08-01 00:00:00');--7

7. Time Zone 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

Summarize

Here is a brief introduction, often use the time operation of the function, of course, there are many do not take into account, this can only in the future in the actual, slowly to find.

Reference articles

Http://www.cnblogs.com/ggjucheng/p/3352280.html

MySQL Date function