SQLite 'DATETIME' adapter timezone awareness

[Pedro Romano]

Needing timezone aware 'DateTime' columns in my 'SQLite' database, I
noticed that the aware 'datetime's I was storing in the SQLite
database in the 'DateTime(timezone=True)' columns were being stored as
naive timestamps without timezone information.

I also checked that in the source code
('sqlalchemy.dialects.sqlite.DATETIME' class) type adapter
implementation the conversion to string is omitting the timestamp, so
obviously any 'datetime's with timezone information will lose it when
persisted by 'SQLAlchemy'. Is this by design?

Thanks in advance for any feedback on this issue.

--Pedro.

[Michael Bayer]

I would qualify that as a missing feature. The "timezone=True" flag is so far only implemented for Postgresql's TIMESTAMP type which features this option natively. SQLite's type could support this flag as well so I've added ticket #1985.

For now you'd have to subclass sqlite.DATETIME and provide an alternate bind/result processor.

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Domingo Aguilera]

Somewhat related to this Michael,

is there a way to express a server_default meaning "native engine's
timestamp" for DateTime columns? A value that would mean getutcdate()
in sql server, current_timestamp in postgresql and so on?

[Michael Bayer]

usually server_default=func.now() accomplishes this - that will use localtime. For utc we don't have a backend-agnostic UTC function as of yet, you can create them pretty easily with @compiles.

[Pedro Romano]

Thanks for the quick reply Michael. I'll implement the workaround as
suggested and wait for ticket #1985 to be implemented.