MySQL convert date string to Unix timestamp
System Design practice on Codemia
Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.
MySQL, a powerful and widely used relational database management system, provides a variety of functions for date and time manipulation. A common requirement in data processing is the conversion of human-readable date strings into Unix timestamps, which represent the number of seconds that have elapsed since the Unix epoch (January 1, 1970). This conversion is essential for applications that require date comparison or precise time calculations. This article will explain how to convert a date string to a Unix timestamp in MySQL, provide examples and technical explanations, and offer insight into associated functions.
Converting Date Strings to Unix Timestamps in MySQL
Using the UNIX_TIMESTAMP() Function
MySQL offers the UNIX_TIMESTAMP() function, which converts a date or datetime expression to a Unix timestamp. Its behavior changes based on how it's used:
- Without Arguments: Returns the current time as a Unix timestamp.
- With a Date Argument: Converts the provided date or datetime string to the Unix timestamp.
Basic Usage
Assume that we have a date string '2023-10-15 10:30:45', and we want to convert it into a Unix timestamp. Here's how you can do this:
- Input Date Format: The
UNIX_TIMESTAMP()function requires the date to be in a recognized format, notablyYYYY-MM-DD HH:MM:SSfor full datetime strings. If the format is incorrect, it may lead to unexpected results orNULL. - Time Zone Considerations: The conversion is done using the timezone set on the MySQL server. If your application or data does not use the server's timezone, it may result in incorrect timestamps.
- Handling Defaults: If you input a malformed date, MySQL will often return
NULL. STR_TO_DATE(date_string, format): Parses the date string based on the specified format string. This example uses%d-%m-%Y %H:%i:%sto match the'15-10-2023 10:30:45'format.- Leap Years and Daylight Saving: Dates like February 29th during non-leap years or timestamps falling on Daylight Saving Time shifts require proper handling to ensure the validity and accuracy of results.
- NULL Dates: If the provided date string isn’t valid or is empty, MySQL will often yield a
NULLvalue for theUNIX_TIMESTAMP()result. FROM_UNIXTIME(): This function performs the opposite operation, converting a Unix timestamp back into a readable date format.- Time Zone Functions: For applications working across multiple time zones, MySQL's
CONVERT_TZ()function can adjust timestamps accordingly.
Related reading
- MySQL COUNT DISTINCT
- Mysql count rows using filters on high traffic database
- MySQL 'create schema' and 'create database' - Is there any difference
- MySQL Data - Best way to implement paging?
- MySQL data directory location
- mysql database Multi-master replication on dynamic ip
- mysql database replication error log. Ways to retrieve it
- MySQL Database won't start in XAMPP Manager-osx

System Design Fundamentals
Build a strong foundation in designing scalable, reliable distributed systems.
View the courseTrack what you have practised
A free account saves your progress, solutions and study plan across every problem on Codemia.
System Design practice on Codemia
Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.