CODEWITHSUNDEEP
pythonmysqldatetime
Cyril N.
Cyril N.

Reputation: 39889

Difference in datetime => timestamp only in some cases?

While trying to compare values between Python and MySQL, I came across a very odd bug.

I have a "created" column in the database (of type "datetime"), and I have for instance these two values:

"2018-03-15 10:11:47"
"2018-10-28 02:06:08"

When converted to Timestamp using UNIX_TIMESTAMP(created), I get the following:

1521105107
1540688768

Now, if I recreate these dates in Python, and get the timestamp (Python3), I get the following:

For the first date:

dt = datetime.datetime(year=2018, month=3, day=15, hour=10, minute=11, second=47)
print(int(dt.timestamp()))
$> 1521105107 # Same as the database

For the second date:

dt = datetime.datetime(year=2018, month=10, day=28, hour=2, minute=6, second=8)
print(int(dt.timestamp()))
$> 1540685168 # Different!

Why? What is happening?

If I had any delta on the database or Python because of the timestamp, it would be for every line, not a few.

I'm suspecting the database is keeping track of some timezone for the created column, but not displayed, but I'm stuck there ...

Upvotes: 1

Views: 123

Answers (1)

Booboo
Booboo

Reputation: 44283

You need to make sure that when dealing with a DATETIME column that the timezone being used by your MySql server is the same as the one being used by your Python environment, specifically when you are initializing that column with a value produced from a function call such as NOW() or, for example, when you are converting that column with a call to UNIX_TIMESTAMP.

So, the first thing I do when I connect is to issue a command such as 'set time_zone = 'America/New_York'; (but only because I LOVE New York; you might LOVE some other place) and then I don't need to worry if my server is in Utah or in New York nor do I need to know when daylight savings time is in effect. If you find that a timezone such as 'America/New_York' is unavailable on your server, then you should look at Time zone description tables. Secondly, you should install Python package pytz. You will then have access to many additional timezones. And use an explicit timezone when constructing a datetime.datetime object! For all I know, my ISP's Python environment may not have the same exact timezone setting as its MySql server and neither is what I would want to use anyway. Hopefully, if you follow these guidelines, these discrepancies should disappear. if not, I'll give your money back.

Upvotes: 1

Related Questions