Return timezone aware postgres fields as a string

Question

I'd like to use the create_engine parameters to get my timezone aware data out (in python), not as a datetime.datetime, but as a string representation of the UTC time it stores.

Specifically, I have:

engine = create_engine(DB_URI,
                       convert_unicode=True,
                       json_serializer=json.dumps)

conn = engine.connect()
res = find_all(conn, myquery)

But my dates come back like this:

datetime.datetime(2020, 4, 14, 7, 30, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)

While I expect it to look more like:

2020-04-14 07:30Z

What can I change so that sqlalchemy will retrieve these values as text timestamps?

I expected the following to work as well:

PGTZ = psycopg2.extensions.new_type(
    psycopg2.extensions.DATEARRAY.values,
    'PGTZ',
    lambda value, curs: value.isoformat() if value is not None else None)

psycopg2.extensions.register_type(PGTZ)

But this had no effect.

I've consulted other references:

Timezone aware datetime to string? This doesn't help because it converts after query time, which is not what I'm looking for
https://access.crunchydata.com/documentation/psycopg2/2.7.5/extras.html#psycopg2.extras.register_tstz_w_secs This is promising
https://www.postgresql.org/message-id/20130801121119.GA10036%40llserver.lakeliving.com This is about storage, not about retrieval
https://stackoverflow.com/a/43121970/1052117 This is close, but is about flask
https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSON this is close but serializes json in particular.

Jiří Baum · Accepted Answer · 2020-05-07 23:44:40Z

1

Try the cast() or type_coerce() functions?

The cast() function will use an explicit type conversion in the database, while the type_coerce() will only treat it as that type on retrieval into Python.

answered May 7, 2020 at 23:44

Jiří Baum

6,9882 gold badges19 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mittenchops Over a year ago

Can you help me understand how/where to use these?

Jiří Baum Over a year ago

In the select() part of the query? The manual has examples for both o them...

Collectives™ on Stack Overflow

Return timezone aware postgres fields as a string

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related