How to allow sqlite3 to store tzinfo?

Question

I'm trying to store a tzinfo got from tzlocal.getlocalzone() into a sqlite3 table.

How can I make sqlite accept tzinfo type?

import sqlite3
import datetime
import pytz
from tzlocal import get_localzone

db = sqlite3.connect("tztestings.sqlite", detect_types=sqlite3.PARSE_DECLTYPES)
db.execute("CREATE TABLE IF NOT EXISTS history (time TIMESTAMP NOT NULL, timediff TZINFO NOT NULL,"
           " account TEXT NOT NULL, amount INTEGER NOT NULL, PRIMARY KEY (time, account))")

Answer 1

It seems to me that you want to get back the tzinfo some later time from your SQLite database. So the answer is you cannot.

But SQLite does not forbid you to store a blob into a column. So you can pickle the tzinfo and store it, or make up your own representation (i.e., a string of timezone info) and store it. When you retrieve from the database, just unpickle or rebuild the tzinfo from your representation.

Pickled version may not be useful in some cases, especially you want to support some computation (e.g., within 1 hour difference from a given timezone) in SQL statement.