How to use MySQL timestamp to query Time Periods

Common functions used to query time periods using MySQL timestamps

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 () is called with a date parameter, it returns the second value starting from '2017-01-01 00:00:00 'GMT. Date can be a number of a date string, A datetime string, a timestamp, or a local time in yymmdd or yyyymmdd format.

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 whether the function is used in a string or number 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 the same modifier as the entries listed by the date_format () function.

Mysql> select from_unixtime (unix_timestamp (), '% Y % d % m % H: % I: % S % x ');

-> '2014 23rd December 03:43:30 x'

The unix_timestamp function is used to convert the date data in the MySQL database to an integer number in the form of UNIX timestamp: Select unix_timestamp ('2017-02-28 ') testdate;