How to convert Python date format to 10-digit date format for mysql?

While working with databases like MySQL, it's necessary to store dates in a numeric format, especially for timestamps. MySQL commonly uses Unix timestamps, which are 10-digit numbers representing the number of seconds since January 1, 1970 (known as the epoch).

The following are the different methods from the time and datetime modules to convert Python date formats into a 10-digit format (Unix timestamp) suitable for use with MySQL.

Using mktime() Method

The mktime() method from the Python time module is the inverse function of the localtime(). This method converts a time.struct_time object or a tuple with 9 elements into seconds since the epoch (January 1, 1970) of the local system.

Syntax

The syntax of the mktime() method is as follows −

time.mktime(t)

Where t is a time.struct_time object or a tuple containing 9 elements corresponding to the time.struct_time object. If the input value does not represent a valid time, either an OverflowError or a ValueError will be raised.

Example

In this example, we use the date.today() method to get the current date and convert it into a 10-digit timestamp using mktime() −

import time
from datetime import date

my_date = date.today()
my_date_tuple = my_date.timetuple()
print("Time tuple:", my_date_tuple)
print("Unix timestamp:", int(time.mktime(my_date_tuple)))

The output of the above code is −

Time tuple: time.struct_time(tm_year=2024, tm_mon=12, tm_mday=19, tm_hour=0, tm_min=0, tm_sec=0, tm_wday=3, tm_yday=354, tm_isdst=-1)
Unix timestamp: 1734566400

Using strptime() + mktime() Combination

If the date is given as a string format (e.g., "01-01-18"), then we need to parse it into a time object before converting it. This can be done by using the strptime() function to convert the string into a structured time object.

Example

In the following example, we parse a date string "01-01-18" into a datetime object and then convert it to seconds since epoch −

import time

my_time = time.strptime("01-01-18", "%d-%m-%y")
print("Parsed time:", my_time)
print("In 10-digit date format:", int(time.mktime(my_time)))

The output of the above code is −

Parsed time: time.struct_time(tm_year=2018, tm_mon=1, tm_mday=1, tm_hour=0, tm_min=0, tm_sec=0, tm_wday=0, tm_yday=1, tm_isdst=-1)
In 10-digit date format: 1514764800

Using timestamp() Method

Compared to other methods, using timestamp() from the Python datetime module is the most straightforward way to convert a datetime object into 10-digit format (Unix timestamp). It returns a floating-point number, which we can convert to an integer.

Example

The following program converts a datetime object representing October 17, 2021, to a Unix timestamp −

import datetime

date_time = datetime.datetime(2021, 10, 17)
unix_timestamp = int(date_time.timestamp())
print("DateTime object:", date_time)
print("Unix timestamp:", unix_timestamp)

The output of the above code is −

DateTime object: 2021-10-17 00:00:00
Unix timestamp: 1634428800

Comparison

Conclusion

Use timestamp() for datetime objects as it's the most direct method. Use strptime() + mktime() for converting date strings. All methods produce Unix timestamps compatible with MySQL storage.