How to use time functions in mysql

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.