MySQL Time function

Http://blog.sina.com.cn/s/blog_6d39dc6f0100m7eo.html

Functions and keywords are not case-sensitive in MySQL. The datetime parameter of the following function can be used either as a time string or as a time variable or an expression.

First, get the current time

The following methods are equivalent, the string environment output format is: Yyyy-mm-dd HH:MM:SS, the digital environment output format is: YYYYMMDDHHMMSS

Now () Current_timestamp ()/Current_timestamplocaltime ()/localtime localtimestamp ()/localtimestamp

Sysdate ()

When the other method executes, the value is obtained at the beginning, and the Sysdate () method dynamically obtains the value in the function.

Curdate ()

Current_date ()/ current_date

These two methods only get the date, the string environment returns the HH:MM:SS format, and the HMMSS format is returned in the digital environment

Curtime ()

Current_time ()/ current_time

These two methods only get the time, the YYYYMMDD format is returned in the digital environment

Utc_date ()/utc_date

Utc_time ()/utc_time

Utc_timestamp ()/Utc_timestamp

return UTC time (World unification time, 0 time zone, China in East 8 district, so slower than our local time 8 hours, local time =UTC time + 8 hours)

Second, access to time part of the data

Date (DateTime)

Return Date Yyyy-mm-dd

Time (DateTime)

Return Time Hh:mm:ss

Year (datetime)

Quarter (DateTime)

Month (DateTime)

MonthName (DateTime)

Day (DateTime)

Hour (DateTime)

Minute (DateTime)

Second (DateTime)

Microsecond (DateTime)

Dayname (DateTime)

Returns the name of the day of the week

Week (DateTime)

Equals Week (datetime,0)

Week (datetime, Mode)

...

Mode	the first day of the week	Range	Week 1 for the first week
0	Sunday	0~53	There's a Sunday in the year.
1	Monday	0~53	More than 3 days in the year
2	Sunday	1~53	There's a Sunday in the year.
3	Monday	1~53	More than 3 days in the year
4	Sunday	0~53	More than 3 days in the year
5	Monday	0~53	There's a Monday in the year.
6	Sunday	1~53	More than 3 days in the year
7	Monday	1~53	There's a Monday in the year.

Yearweek (DateTime)

Number of years and weeks

Extract ( unit from datetime)

Unit value: Year, quarter, month, week, day, hour, minute, second, microsecond

Year_month, Day_hour, Day_minute, Day_second, Day_microsecond, Hour_minute, Hour_second, Hour_microsecond, Minute_ Second, Minute_microsecond, Second_microsecond

The value in the XXX_ZZZ format represents the selected range xxxyyyzzz, not just the two items.

DayOfWeek (DateTime)

DateTime is the day of the week, (Sunday is the first of one weeks, ODBC standard)

DayOfMonth (DateTime)

DateTime is the first day of January, 1-31

DayOfYear (DateTime)

DateTime is the day ordinal of a year, 1-366

WeekDay (DateTime)

DateTime is the day of the week, (Monday is the No. 0 day of one weeks)

WeekOfYear (DateTime)

DateTime is the week ordinal of a year, equal to Week (datetime,3).

Last_day (date)

Returns the last day of the month

Three, time operation

+/-Operator

Select "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
-1998-01-01 00:00:00

Select INTERVAL 1 day + "1997-12-31";
1998-01-01

Select "1998-01-01"-INTERVAL 1 SECOND;
-1997-12-31 23:59:59

date_add (datetime, Interval expr unit)

Unit:day, hour, minute, second, microsecond, week, month, quarter, year

Year_month, Day_hour, Day_minute, Day_second, Day_microsecond, Hour_minute, Hour_second, Hour_microsecond, Minute_ Second, Minute_microsecond, Second_microsecond

The value in the XXX_ZZZ format represents the selected range xxxyyyzzz, not just the two items.

Example:

Select Date_add (now (), INTERVAL 3 day)

Select Date_add (now (), INTERVAL-2.5 Hour)

Select Date_add (now (), INTERVAL ' 01:15:30 ' Hour_second)

Select Date_add (now (), INTERVAL '-2 01:15:30 ' Day_second)

Adddate (datetime, INTERVAL expr unit)

Adddate (datetime, Days)

Addtime (EXPR1, EXPR2)

date_sub (datetime, Interval expr unit)

Usage with Date_add (datetime, Interval expr unit)

Subdate (datetime, INTERVAL expr unit)

Subdate (datetime, Days)

Subtime (EXPR1, EXPR2)

Timestampadd (uint, interval, datetime_expr)

Period_add (P,n)

Add n months to a stage p,p format of yymm or yyyymm

Select Period_add (9801,2); --199803

Period_diff (P1,P2)

Returns the number of months between P1 and P2, p in the form of yymm or yyyymm

DateDiff (datetime1, datetime2)

Returns the difference in days

Timediff (datetime1, datetime2)

Return time difference (HH:MM:SS), two parameter formats must be the same

Timestampdiff (unit,datetime1,datetime2)
Returns an integer difference between a date or datetime expression Datetime_expr1 and datetime_expr2the.

The unit parameter specifies the result unit, with the available values:

• Frac_second ms
• SECOND sec
• MINUTE min
• HOUR hours
• Day days
• WEEK Week
• Month months
• QUARTER Quarter
• Year

Example statements:

SELECT Timestampdiff (SECOND, ' 2012-12-21 ', Current_timestamp ()) as ' SECOND ';

SELECT * from Autotesting_mobiledevice where Timestampdiff (SECOND, Mdlastsynchtime, Now ()) <6000;

Precautions:

The time difference between units in seconds is based on the second.

The time difference between days is based on 24*60*60 (86400). If the interval is less than 86,400 seconds, then 0, or 1 if >=86400 seconds and <86400*2 seconds.

If the unit is in minutes, the calculated interval minutes are based on 60 seconds. If the interval is less than 60 seconds, then 0, or 1 if >=60 seconds and <60*2 seconds.

Select Timestampdiff (Day, "2012-08-22 15:15:15", "2012-08-23 15:15:16");
1

Select Timestampdiff (Day, "2012-08-22 15:15:17", "2012-08-23 15:15:16");
0

Three, time conversion

Time_to_sec (DateTime)

Returns the total number of seconds

Sec_to_time (seconds)

To_days (date)

From_days (days)

Makedate (Year,dayofyear)

Maketime (Hour,minute,second)

Str_to_date (Str,format)

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

Format

%M month name (January ... December)
%W Week name (Sunday ... Saturday)
%d The date of the month with English prefixes (1st, 2nd, 3rd, etc.). ）
%Y year, number, 4 bit
%y year, number, 2 bit
%a abbreviated weekday name (Sun ... Sat)
Number of days in the month of%d, number (00 ...). 31)
Number of days in%e month, number (0 ... 31)
%m Month, number (01 ... 12)
%c month, number (1 ... 12)
%b Abbreviated month name (Jan ... DEC)
%j Days of the year (001 ... 366)
%H hours (00 ... 23)
%k hours (0 ... 23)
%h hours (01 ... 12)
%I Hours (01 ... 12)
%l hours (1 ... 12)
%i minutes, Numbers (00 ... 59)
%r time, 12 hours (Hh:mm:ss [ap]m)
%T time, 24 hours (HH:MM:SS)
%s seconds (00 ... 59)
%s seconds (00 ... 59)
%p am or PM
%w days in one weeks (0=sunday ... 6=saturday)
%u Week (0 ... 52), here Sunday is the first day of the week
%u Week (0 ... 52), here Monday is the first day of the week
Percent of a text "%".
All other characters are not interpreted as being copied into the results.

Date_format (Date,format)

Time_format (Time,format)

Get_format (date|time|datetime, ' eur|usa|jis|iso|internal ')

Get the time format for a country region

Unix_timestamp ()

Returns from 1970-1-1 0:00:00 UTC (because China is East eight, so it is 8:00:00.) The number of seconds to start until the current system time

Unix_timestamp (DateTime)

Time earlier than 1970-1-1 0:00:00 UTC, Return 0

From_unixtime (Unix_timestamp)

The string environment output format is: Yyyy-mm-dd HH:MM:SS, the digital environment output format is: YYYYMMDDHHMMSS

From_unixtime (unix_timestamp, format)

Convert_tz (datetime, From_tz, To_tz)

Time zone conversion can also be achieved through functions such as Date_add (), Date_sub (), Timestampadd (), etc.

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

MySQL Time function