Custom utcnow function and session.execute yields a constant timestamp

[ngo...@virtual-gate.net]

Hi,

I've created the following utcnow function as described here:  http://docs.sqlalchemy.org/en/latest/core/compiler.html?highlight=utc#utc-timestamp-function

from sqlalchemy.ext.compiler import compiles

from sqlalchemy.sql import expression

from sqlalchemy.types import DateTime, String

class utcnow(expression.FunctionElement):

    """A custom SQLAlchemy function that returns the current UTC time"""

    type = DateTime()

@compiles(utcnow, 'postgresql')

def pg_utcnow(element, compiler, **kw):

    """Adds a postgres implementation of UTC now function

    """

    return "TIMEZONE('utc', CURRENT_TIMESTAMP)"

Whenever I run db.session.execute(utcnow()), I'm getting the exact same date:

In [12]: db.session.execute(custom_functions.utcnow()).scalar()

Out[12]: datetime.datetime(2018, 3, 28, 18, 28, 49, 879360)

In [13]: db.session.execute(custom_functions.utcnow()).scalar()

Out[13]: datetime.datetime(2018, 3, 28, 18, 28, 49, 879360)

...

I guess its something trivial that I'm doing wrong. any ideas?

[Mike Bayer]

try calling it in a new transaction, it's likely constant per transaction time.

$ psql -U scott test
psql (9.6.8)
Type "help" for help.

test=# begin;
BEGIN
test=# select now();
now
------------------------------
2018-03-28 15:03:09.82421-04
(1 row)

test=# select now();
now
------------------------------
2018-03-28 15:03:09.82421-04
(1 row)

test=# select now();
now
------------------------------
2018-03-28 15:03:09.82421-04
(1 row)

test=# rollback;
ROLLBACK
test=# select now();
now
-------------------------------
2018-03-28 15:03:30.023129-04
(1 row)

test=# select now();
now
-------------------------------
2018-03-28 15:03:31.906217-04
(1 row)

test=#

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Jonathan Vanasco]

in postgres

returns the start of the current transaction:

NOW()

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

LOCALTIME

LOCALTIMESTAMP

returns the actual time:

clock_timestamp()

timeofday()

[Nadav Goldin]

Thats the issue indeed, thanks!

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---

> You received this message because you are subscribed to a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/Dxnqw2knn5o/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to