Re: [sqlalchemy] Retrieve Dates as UTC
As far as I can tell that is just for setting the time. By the linked
documentation, using server_default.
Postgres stores all datetimes as UTC and then does the conversion on query,
depending on the timezone set in the connection. This defaults to the
computer's timezone.
In my submission it sets the timezone on all the connections in the pool.
From then on all datetimes returned in queries are UTC regardless of the
database configuration. This saves a configuration dependency while still
allowing other connections/applications to query in other timezones if
needed. The other added benefit is that it only happens once during
connection setup, not at every query call.
On Wednesday, 5 June 2013 14:44:23 UTC-7, Michael Bayer wrote:
we do this (for PG and SQL Server):
class utcnow(expression.FunctionElement):
key = 'utcnow'
type = DateTime()
@compiles(utcnow, 'postgresql')
def _pg_utcnow(element, compiler, **kw):
return TIMEZONE('utc', CURRENT_TIMESTAMP)
@compiles(utcnow, 'mssql')
def _ms_utcnow(element, compiler, **kw):
return GETUTCDATE()
you then use utcnow() as your SQL expression.
just remembered i put it in the docs too:
http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html#utc-timestamp-function
On Jun 5, 2013, at 4:51 PM, Owen M owen...@gmail.com javascript:
wrote:
Was hunting around for how to do this, and couldn't find any examples, but
eventually figured it out.
Goal is to have all dates coming out of Postgres to be in UTC, regardless
of the DBA's timezone configuration.
Key piece of code is as follows:
import sqlalchemy
def set_utc_on_connect(dbapi_con, con_record):
c = dbapi_con.cursor()
c.execute(SET TIME ZONE UTC)
c.close()
sqlalchemy.event.listen(sqlalchemy.pool.Pool, 'connect',
set_utc_on_connect)
Now all connections in the pool will have their timezone set to UTC, so
all queries will return UTC for datetime objects with timezone specified.
Just posting it here incase someone else needed the info.
Cheers,
~Owen
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.comjavascript:
.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Retrieve Dates as UTC
Was hunting around for how to do this, and couldn't find any examples, but eventually figured it out. Goal is to have all dates coming out of Postgres to be in UTC, regardless of the DBA's timezone configuration. Key piece of code is as follows: import sqlalchemy def set_utc_on_connect(dbapi_con, con_record): c = dbapi_con.cursor() c.execute(SET TIME ZONE UTC) c.close() sqlalchemy.event.listen(sqlalchemy.pool.Pool, 'connect', set_utc_on_connect) Now all connections in the pool will have their timezone set to UTC, so all queries will return UTC for datetime objects with timezone specified. Just posting it here incase someone else needed the info. Cheers, ~Owen -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Retrieve Dates as UTC
we do this (for PG and SQL Server):
class utcnow(expression.FunctionElement):
key = 'utcnow'
type = DateTime()
@compiles(utcnow, 'postgresql')
def _pg_utcnow(element, compiler, **kw):
return TIMEZONE('utc', CURRENT_TIMESTAMP)
@compiles(utcnow, 'mssql')
def _ms_utcnow(element, compiler, **kw):
return GETUTCDATE()
you then use utcnow() as your SQL expression.
just remembered i put it in the docs too:
http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html#utc-timestamp-function
On Jun 5, 2013, at 4:51 PM, Owen M owenm...@gmail.com wrote:
Was hunting around for how to do this, and couldn't find any examples, but
eventually figured it out.
Goal is to have all dates coming out of Postgres to be in UTC, regardless of
the DBA's timezone configuration.
Key piece of code is as follows:
import sqlalchemy
def set_utc_on_connect(dbapi_con, con_record):
c = dbapi_con.cursor()
c.execute(SET TIME ZONE UTC)
c.close()
sqlalchemy.event.listen(sqlalchemy.pool.Pool, 'connect', set_utc_on_connect)
Now all connections in the pool will have their timezone set to UTC, so all
queries will return UTC for datetime objects with timezone specified.
Just posting it here incase someone else needed the info.
Cheers,
~Owen
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
