0

From an ubuntu 20.04 server I am writing a datetime field to a remote MySQL 5.7 server via Python 3.8.

INSERT INTO
T1
CREATED = datetime.utcnow().strftime("%Y-%m-%d")

The ubuntu server is running at UTC with cron scheduled at 00:30 am. The MySQL Server is Running at UTC+1.

Now the dataset does not contain UTC but the local time offset beeing 01:30 am.

How can I save in UTC?

merlin
  • 2,717
  • 3
  • 29
  • 59
  • This should have all your questions answered https://stackoverflow.com/questions/19023978/should-mysql-have-its-timezone-set-to-utc – mvp Jan 05 '21 at 23:38

2 Answers2

0

If you use a TIMESTAMP data type rather than a DATETIME, MySql will translate from the time zone setting of your connection to UTC when it stores a timestamp, and translate it back when you retrieve it.

So, give a command like

SET time_zone = 'Europe/Paris';

when you first open your MySQL connection, then send your time stamps in local time.

Expressions like MySQL NOW() automatically do this.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Sorry I don't understand. I am saving to a DATETIME data type explicedly with utcnow() because I want to save in UTC. MySQL simply ignores it and saves in local time. – merlin Jan 05 '21 at 23:46
0

you can set the timezone for the connection like this

import mysql.connector

mydb = mysql.connector.connect(
  host="127.0.0.1",
  user="root",
  password="*****",
  database="testdb"
)
mydb._time_zone =  '+00:00'
nbk
  • 45,398
  • 8
  • 30
  • 47
  • Does not seem to work but also does not throw an error. I am testing on my local machine which is not set to UTC. Does this make a difference? – merlin Jan 05 '21 at 23:54
  • as you can see in the documentation https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlconnection-time-zone.html it mis set to change the timezone for the current connection. set the time zone like like in the link and you see it changes then timezone – nbk Jan 05 '21 at 23:58
  • I just realised that I am writing to a DATE data type and it does save UTC correctly for that field, the next column is DATETIME and it is set to CURRENT_TIMESTAMP on creation (default). I assume I would need to set it explicitly in my statement to be saved in UTC even when the connection is set to UTC? – merlin Jan 06 '21 at 00:16
  • you should always read the manual first see https://dev.mysql.com/doc/refman/8.0/en/datetime.html – nbk Jan 06 '21 at 00:21
  • The confusion here was that there are fields set by the Server and ones that are set by the connection. E.g. on update CURRENT_TIMESTAMP or as default. Those values apear not to be in UTC as I can not set the MySQL server to UTC since that would affect other DBs on that DBMS. – merlin Jan 06 '21 at 09:40