CODEWITHSUNDEEP
pythonsql
notverygood
notverygood

Reputation: 307

Storing struct_time in SQL

Some code I am writing in Python takes in a date from a server in the struct_time format (with the 9 args).

How can I store this date in an SQL database, and be able to read back this date as a struct_time while keeping the timezone and all additional information coming from struct_time?

I tried putting the struct_time directly in the SQL

struct_date = time.struct_time(tm_year=2020, tm_mon=9, tm_mday=10, tm_hour=22, tm_min=31, tm_sec=4, tm_wday=3, tm_yday=254, tm_isdst=0)
cursor.execute("UPDATE dbo.RSS_Links SET last_update=? WHERE link=?;", struct_date, links)
> "A TVP's rows must be Sequence objects.", 'HY000'

I can put the time in the database using the below, but I don't see where the timezone is kept when converting to strftime.

date_to_store = time.strftime("%Y-%m-%d %H:%M:%S", struct_date)

Upvotes: 0

Views: 243

Answers (1)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14097

I'd highly suggest doing one of these (in this specific order):

  1. Use built-in DATETIME data type and store all dates in UTC
  2. Use LONG/BIGINT type to store date in epoch
  3. Use built-in DATETIME format that can store time zone information

Don't store dates as strings, don't couple it with struct_time or any other struct/class, you'll regret it later :)

Your application should have a data layer, which would handle data serialization/deserialization.

Upvotes: 1

Related Questions