How to use time functions in mysql
UNIX_TIMESTAMP ()
UNIX_TIMESTAMP (date)
If no parameter is called, a Unix timestamp (in seconds starting from '2017-01-01 00:00:00 'GMT) is returned ). If UNIX_TIMESTAMP () Uses
The date parameter is called. It returns the second value starting from '2017-01-01 00:00:00 'GMT. Date can be a DATE string, A DATETIME
String, a TIMESTAMP, or a number in the local time format of YYMMDD or YYYYMMDD.
Mysql> select UNIX_TIMESTAMP ();
-> 882226357
Mysql> select UNIX_TIMESTAMP ('2017-10-04 22:23:00 ′);
-> 875996580
When UNIX_TIMESTAMP is used in a TIMESTAMP column, the function accepts the value directly without the implicit "string-to-unix-timestamp" transformation.
FROM_UNIXTIME (unix_timestamp)
Returns the value represented by the unix_timestamp parameter in 'yyyy-MM-DD HH: MM: ss' or YYYYMMDDHHMMSS format, depending on the function being in a string
Or a digital context.
Mysql> select FROM_UNIXTIME (875996580 );
-> '2017-10-04 22:23:00 ′
Mysql> select FROM_UNIXTIME (875996580) + 0;
-> 19971004222300
FROM_UNIXTIME (unix_timestamp, format)
Returns a string representing the Unix time mark, formatted according to the format string. Format can contain entries listed with the DATE_FORMAT () function
The same modifier.
Mysql> select FROM_UNIXTIME (UNIX_TIMESTAMP (),
'% Y % D % M % h: % I: % s % x ');
-> '2014 23rd December 03:43:30 x'
MySQL DATEDIFF () function
Definition and usage
The DATEDIFF () function returns the number of days between two dates.
Syntax
DATEDIFF (date1, date2)
The date1 and date2 parameters are valid date or date/time expressions.
Instance
Example 1
Use the following SELECT statement:
Select datediff ('2014-12-30 ', '2014-12-29') AS DiffDate
Result:
DiffDate
1
Example 2
Use the following SELECT statement:
Select datediff ('2014-12-29 ', '2014-12-30') AS DiffDate
Result:
DiffDate
-1
TO_DAYS:
TO_DAYS is the index of the number of days in a year returned by date.
The following query Selects all records. The value of date_col is within the last 30 days:
Mysql> SELECT something FROM table
WHERE TO_DAYS (NOW ()-TO_DAYS (date_col) <= 30;
DAYOFWEEK (date)
Returns the index of the week of the date (1 = Sunday, 2 = Monday ,...... 7 = Saturday ). These index values correspond to the ODBC standard.
Mysql> select DAYOFWEEK ('2017-02-03 ′);
-> 3
WEEKDAY (date)
Returns the week index of date (0 = Monday, 1 = Tuesday ,...... 6 = Sunday ).
Mysql> select WEEKDAY ('2017-10-04 22:23:00 ′);
-> 5
Mysql> select WEEKDAY ('2017-1997 ′);
-> 2
DAYOFMONTH (date)
Returns the date of a month in the range of 1 to 31.
Mysql> select DAYOFMONTH ('2017-02-03 ′);
-> 3
DAYOFYEAR (date)
Returns the number of days in a year from 1 to 366.
Mysql> select DAYOFYEAR ('2017-02-03 ′);
-> 34
MONTH (date)
Returns the month of date, ranging from 1 to 12.
Mysql> select MONTH ('2017-02-03 ′);
-> 2
DAYNAME (date)
Returns the week name of date.
Mysql> select DAYNAME ("1998-02-05 ");
-> 'Thursday'
MONTHNAME (date)
Returns the month name of date.
Mysql> select MONTHNAME ("1998-02-05 ");
-> 'February'
QUARTER (date)
Returns the quarter of a year from date, ranging from 1 to 4.
Mysql> select QUARTER ('98-04-01 ′);
-> 2
WEEK (date)
WEEK (date, first)
If Sunday is the first day of a week, there is a single parameter that returns the number of weeks of the date, ranging from 0 to 52. Two Parameter formats: WEEK ()
Specify whether the week starts on Sunday or Monday. If the second parameter is 0 and the week starts from Sunday, if the second parameter is 1,
Starting from Monday.
Mysql> select WEEK ('2017-02-20 ′);
-> 7
Mysql> select WEEK ('2017-02-20 ', 0 );
-> 7
Mysql> select WEEK ('2017-02-20 ', 1 );
-> 8
YEAR (date)
Returns the year of date, ranging from 1000 to 9999.
Mysql> select YEAR ('98-02-03 ′);
-> 1998
HOUR (time)
Returns the hour of time, ranging from 0 to 23.
Mysql> select HOUR ('10: 05: 03 ′);
-> 10
MINUTE (time)
Returns the minute of time, ranging from 0 to 59.
Mysql> select MINUTE ('98-02-03 10:05:03 ′);
-> 5
SECOND (time)
The number of seconds for the return time, ranging from 0 to 59.
Mysql> select SECOND ('10: 05: 03 ′);
-> 3
PERIOD_ADD (P, N)
Add N months to phase P (in the format of YYMM or YYYYMM ). Return Value in the format of YYYYMM. Note that the phase parameter P is not a date value.
Mysql> select PERIOD_ADD (9801,2 );
-> 199803
PERIOD_DIFF (P1, P2)
Returns the number of months between period P1 and P2. P1 and P2 should be in the format of YYMM or YYYYMM. Note that the period parameters P1 and P2 are not date values.
Mysql> select PERIOD_DIFF (9802,199703 );
-> 11
DATE_ADD (date, INTERVAL expr type)
DATE_SUB (date, INTERVAL expr type)
ADDDATE (date, INTERVAL expr type)
SUBDATE (date, INTERVAL expr type)
These functions perform date operations. For MySQL 3.22, they are new. ADDDATE () and SUBDATE () are synonyms of DATE_ADD () and DATE_SUB.