MySQL Time Functions

CURDATE () or CURRENT_DATE () returns the current date in the format of ldquo; YYYY-MM-DDrdquo; or ldquo; YYYYMMDDldquo. Contains only the year, month, and day

CURDATE () or CURRENT_DATE () returns the current date in the format of ldquo; YYYY-MM-DDrdquo; or ldquo; YYYYMMDDldquo. Contains only the year, month, and day

CURDATE () or CURRENT_DATE ()

Returns the current date, in YYYY-MM-DD or YYYYMMDD format. Contains only the year, month, and day

Select curdate () + 0;

Result: 20140214

Select curdate () + 1;

Result: 20140215

CURTIME () or CURRENT_TIME ()
Returns the current time, including the hour, minute, and second.

NOW ()
Returns the current time, including the date and time.

UTC_DATE Function
Returns UTC time, Beijing time = UTC time + 8 hours

UNIX_TIMESTAMP (), UNIX_TIMESTAMP (date)
If no parameter is called, a Unix timestamp ('2017-01-01 00:00:00 'seconds after GMT) is returned as an unsigned integer. If you use date to call UNIX_TIMESTAMP (), it will return the parameter value in the form of the number of seconds after '2017-01-01 00:00:00 'GMT. Date can be a DATE string, a datetime string, a TIMESTAMP, or a number in the YYMMDD or YYYMMDD format of the local time.

Mysql> SELECT UNIX_TIMESTAMP ();
-> 882226357

Mysql> SELECT UNIX_TIMESTAMP ('2017-10-04 22:23:00 ');
-> 875996580

FROM_UNIXTIME ()
Returns the date value of the unix timestamp.

Mysql> SELECT FROM_UNIXTIME (875996580 );
-> '2017-10-04 22:23:00'

TO_DAYS (date)
Returns the number of days for a given date (the number of days starting from 0 in the year ).

Mysql> SELECT TO_DAYS (950501 );
-> 728779

Mysql> SELECT TO_DAYS ('2017-10-07 ');
-> 729669

DATEDIFF Function
Datediff (date1, date2) is used to calculate the number of days between two dates.

DATE_ADD function or ADDDATE Function
Date_add (date, interval expr type): returns the date that differs from the given date in the interval period.

Mysql> select date_add (curdate (), interval 1 day)
> 2014-02-15
Mysql> select date_add (curdate (), interval-1 day)
> 2014-02-13

DATE_SUB function or SUBDATE Function
Date_sub (date, interval expr type): returns the date of the expr type earlier than the given date.

Mysql> select date_sub (curdate (), interval 1 day)
> 2014-02-13
Mysql> select date_add (curdate (), interval-1 day)
> 2014-02-15

MySQL allows punctuation separators in any expr format. The recommended Delimiter is displayed in the table. If the date parameter is a DATE value, and your calculation only includes the YEAR, MONTH, and DAY sections (that is, there is no time section), the result is a DATE value. Otherwise, the result is a DATETIME value.
If the expression at the other end is a date or datetime value, INTERVAL expr type is only allowed at both ends of the + operator. For the-operator, INTERVAL expr type is only allowed on the right side, because it is meaningless to extract a date or datetime value from a time INTERVAL. (See the example below ).

Mysql> SELECT '2014-12-31 23:59:59 '+ INTERVAL 1 SECOND;
-> '2017-01-01 00:00:00'
Mysql> select interval 1 DAY + '2017-12-31 ';
-> '2017-01-01'
Mysql> SELECT '2014-01-01 '-INTERVAL 1 SECOND;
-> '2017-12-31 23:59:59'

The EXTRACT () function is used to return a separate part of a date or time, such as year, month, day, hour, or minute.

The EXTRACT (unit FROM date) date parameter is a valid date expression. The unit parameter can be the following values:

Unit Value

MICROSECOND

SECOND

MINUTE

HOUR

DAY

WEEK

MONTH

QUARTER

YEAR

SECOND_MICROSECOND

MINUTE_MICROSECOND

MINUTE_SECOND

HOUR_MICROSECOND

HOUR_SECOND

HOUR_MINUTE

DAY_MICROSECOND

DAY_SECOND

DAY_MINUTE

DAY_HOUR

YEAR_MONTH

DATE_FORMAT (date, format)
Format the date value based on the format string
(The flag is available in the format string:
% M month name (January ...... December)
% W name of the Week (Sunday ...... Saturday)
% D indicates the date of the month with an English prefix (1st, 2nd, 3rd, and so on .)
% Y year, number, 4 digits
% Y year, number, 2 digits
% A abbreviated name of the Week (Sun ...... Sat)
% D number of days in the month (00 ...... 31)
% E number of days in the month (0 ...... 31)
% M month, number (01 ...... 12)
% C month, number (1 ...... 12)
% B abbreviated month name (Jan ...... Dec)
% J days in a year (001 ...... 366)
% H hour (00 ...... 23)
% K hour (0 ...... 23)
% H hour (01 ...... 12)
% I hour (01 ...... 12)
% L hour (1 ...... 12)
% I minute, number (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 a week (0 = Sunday ...... 6 = Saturday)
% U Week (0 ...... 52). Sunday is the first day of the week.
% U Week (0 ...... 52) Monday is the first day of the week.
% Characters %)

Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % W % M % y ');
-> 'Saturday October 1997'
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % H: % I: % s ');
-> '22: 23: 00'
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % D % y % a % d % m % B % J ');
-> '4th 97 Sat 04 10 Oct 123'
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % H % k % I % r % T % S % W ');
-> '22 22 10 10:23:00 PM 22:23:00 6'

LAST_DAY ()
Returns the last day of the month.

> SELECT LAST_DAY ('2017-02-14 ');
> '2017-02-28'