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