Python: Transform SQL Datetime to Unix Timestamp

Data and Databases
1

I’m working with a database that gives me dates in SQL format. Here’s what I get when I run a query:

+---------------------+
| timestamp           |
+---------------------+
| 2010-03-15 14:30:00 |
+---------------------+

In my Python script, I fetch this data like this:

cursor.execute(my_query)
result = cursor.fetchall()
print(result)

The output looks like this:

({'timestamp': datetime.datetime(2010, 3, 15, 14, 30, 0)},)

I need to turn this into a Unix timestamp. What’s the best way to do this in Python? I’m not sure if I should use a built-in function or write my own conversion method. Any help would be great!

2

Converting SQL datetime to Unix timestamp in Python is straightforward. You can use the datetime module’s timestamp() method. Here’s a concise solution:

from datetime import datetime

sql_datetime = result[0]['timestamp']
unix_timestamp = int(sql_datetime.timestamp())

print(unix_timestamp)

This code extracts the datetime object from your query result, converts it to a Unix timestamp (seconds since epoch), and rounds it to an integer. The timestamp() method handles timezone conversions automatically, assuming your SQL datetime is in UTC. If it’s not, you might need to adjust for the timezone difference before conversion.

I’ve used this approach in several projects dealing with time series data from databases, and it’s proven reliable and efficient.

3

hey there! you can use the time module for this. It’s pretty simple:

import time

sql_datetime = result[0]['timestamp']
unix_timestamp = int(time.mktime(sql_datetime.timetuple()))

this converts ur datetime to unix timestamp. hope it helps! lemme know if u need anything else

4

ooh, interesting question! have you considered using the calendar module? it’s kinda neat for this stuff. something like:

import calendar

unix_time = calendar.timegm(result[0]['timestamp'].timetuple())

what do you think? does this fit your needs? curious to hear how it goes!