datetime to utc

[Mason]

Hi

I have the following statement

rows = self.session.query(e.src_id, e.tar_id, \
e.type, m.text, e.event_ts).\
outerjoin(m, e.media_id==m.message_id).\
filter(e.src_id==src_id).\
filter(e.tar_id==tar_id).\
all()[start:offset]

Some of the results are like

(2L, 1L, 3, None, datetime.datetime(2012, 1, 13, 14, 52, 58))
(2L, 1L, 3, None, datetime.datetime(2012, 1, 13, 14, 52, 58))
(2L, 1L, 5, None, datetime.datetime(2012, 1, 13, 14, 52, 59))

event_ts is a Datetime object. Is it possible to convert this to utc
in the statement? I can do this directly with the mysql select
statement, but not sure about if this is possible in sqlalchemy

TIA
-mason

[Michael Bayer]

you'd need to use func.<something that does utc>(date), let's check mysql's docs... convert_tz: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz

so

from sqlalchemy import func

session.query(func.convert_tz(e.event_ts, 'EST', 'UTC'))

[Mason]

This is great!! Wasn't aware of this at all. Is the <something that
does utc> part db dependent? I am only familiar with mysql, so if I
change to other db in the future, and the <convert to utc> function is
called 'convert_to_tz' instead of 'convert_tz', will this break? I
know I can access the named tuple using the following before.

for r in rows
print r.event_ts

Now with the func.convert_tz(), can I use something like
func.convert_tz() AS ts, so I can reference it with r.ts?

On Jan 16, 7:46 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Jan 16, 2012, at 8:34 PM, Mason wrote:
>
>
>
>
>
>
>
>
>
> > Hi
>
> > I have the following statement
>
> >            rows = self.session.query(e.src_id, e.tar_id, \
> >                        e.type, m.text, e.event_ts).\
> >                        outerjoin(m, e.media_id==m.message_id).\
> >                        filter(e.src_id==src_id).\
> >                        filter(e.tar_id==tar_id).\
> >                        all()[start:offset]
>
> > Some of the results are like
>
> > (2L, 1L, 3, None, datetime.datetime(2012, 1, 13, 14, 52, 58))
> > (2L, 1L, 3, None, datetime.datetime(2012, 1, 13, 14, 52, 58))
> > (2L, 1L, 5, None, datetime.datetime(2012, 1, 13, 14, 52, 59))
>
> > event_ts is a Datetime object.  Is it possible to convert this to utc
> > in the statement?  I can do this directly with the mysql select
> > statement, but not sure about if this is possible in sqlalchemy
>

> you'd need to use func.<something that does utc>(date), let's check mysql's docs... convert_tz:http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#f...

[Michael Bayer]

On Jan 17, 2012, at 1:35 PM, Mason wrote:

> This is great!! Wasn't aware of this at all. Is the <something that
> does utc> part db dependent? I am only familiar with mysql, so if I
> change to other db in the future, and the <convert to utc> function is
> called 'convert_to_tz' instead of 'convert_tz', will this break? I
> know I can access the named tuple using the following before.
>
> for r in rows
> print r.event_ts
>

The more involved time functions like TZ conversion and date arithmetic are database dependent. When you need to do things with dates in a platform-agnostic way, usually using the @compiles system to construct the set of functions that you need is the most direct route. In fact there's an example regarding UTC right here: http://www.sqlalchemy.org/docs/core/compiler.html#utc-timestamp-function

> Now with the func.convert_tz(), can I use something like
> func.convert_tz() AS ts, so I can reference it with r.ts?

func. returns a column expression just like a column itself, all of which have label(), so func.convert_tz(mycolumn, ..., ..).label('ts')