Convert Dates to Timestamps in MySQL

In MySQL, you can convert dates to UNIX timestamps using the UNIX_TIMESTAMP() function, which converts a date into the number of seconds since January 1, 1970.

In this tutorial, you will learn how to use the UNIX_TIMESTAMP() function to convert dates to timestamps in MySQL.

Converting Dates to Timestamps in MySQL using UNIX_TIMESTAMP()

Converting a date to a timestamp in MySQL allows you to work with date and time data in a numeric format. This can be particularly useful for date comparisons or for calculating the time elapsed between two dates. The UNIX_TIMESTAMP() function provides an easy way to perform this conversion.

Examples: Converting Dates to Timestamps in MySQL

Here are some examples that demonstrate how to use the UNIX_TIMESTAMP() function to convert dates to timestamps.

1. Converting a Specific Date to a Timestamp

To convert a specific date, such as '2024-11-09', to a timestamp:

</>
Copy
SELECT UNIX_TIMESTAMP('2024-11-09') AS timestamp_value;

This query converts '2024-11-09' to its UNIX timestamp, resulting in a value like 1731024000.

Convert Dates to Timestamps in MySQL - Example: convert a specific date, such as 2024-11-09, to a timestamp

2. Converting Dates and Times to Timestamps

If you have a date with a time component, such as '2024-11-09 14:30:00', you can convert it to a timestamp as follows:

</>
Copy
SELECT UNIX_TIMESTAMP('2024-11-09 14:30:00') AS timestamp_with_time;

This query includes both the date and time, resulting in a more precise timestamp based on the exact moment specified.

Convert Dates to Timestamps in MySQL - Example: Convert date with a time component, such as 2024-11-09 14:30:00, to a timestamp

3. Converting Dates in a Table Column to Timestamps

If you have a table events with a event_date column, you can convert each date to a timestamp:

</>
Copy
SELECT event_name, UNIX_TIMESTAMP(event_date) AS event_timestamp
FROM events;

This query retrieves each event’s date and converts it to a UNIX timestamp, making it easier to perform calculations on dates.