In MySQL, you can use the UNIX_TIMESTAMP()
function to return a Unix timestamp. A Unix timestamp is the number of seconds that have elapsed since ‘1970-01-01 00:00:00’ UTC.
You can use this function to return a Unix timestamp based on the current date/time or another specified date/time.
Syntax
You can use any of the following forms:
UNIX_TIMESTAMP() UNIX_TIMESTAMP(date)
The (optional) date
argument allows you to specify a date for which to calculate the Unix timestamp. If provided, the function returns the value of the argument as seconds since ‘1970-01-01 00:00:00’ UTC.
The date
argument can be a date, datetime, or timestamp string, or a number in YYMMDD, YYMMDDHHMMSS, YYYYMMDD, or YYYYMMDDHHMMSS format.
The return value is an integer if no argument is given or the argument does not include a fractional seconds part, or decimal if an argument is given that includes a fractional seconds part.
Example 1 – Using the Current Date/Time
This example uses the current date and time to produce the Unix timestamp.
SELECT UNIX_TIMESTAMP();
Result:
+------------------+ | UNIX_TIMESTAMP() | +------------------+ | 1530054626 | +------------------+
So that is how many seconds have passed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970, and the time I ran that query.
Example 2 – Specify a Date
In this example, I provide a date for which to calculate the Unix timestamp from.
SELECT UNIX_TIMESTAMP('1970-01-02') As Result;
Result:
+--------+ | Result | +--------+ | 50400 | +--------+
Example 3 – Specify a Datetime Value
In this example, I provide a datetime value.
SELECT UNIX_TIMESTAMP('2021-11-27 12:35:03') AS Result;
Result:
+------------+ | Result | +------------+ | 1637980503 | +------------+
Example 4 – Fractional Seconds
As mentioned, if you provide a fractional seconds part, the return value will be a decimal value (as opposed to integer for the previous examples).
Here’s an example.
SELECT UNIX_TIMESTAMP('2021-11-27 12:35:03.123456') AS Result;
Result:
+-------------------+ | Result | +-------------------+ | 1637980503.123456 | +-------------------+