TimestampNow is inconsistent.

[gmos]

Hi @dantownsend, think that TimestampNow is inconsistent regarding the use of timezones.

The timestamp currently returned is in:

• SQLLite -> UTC (according to doc an checked on my CET machine)
• Postgres -> UTC (checked on a couple of databases in the EU; will be DB TZ setting dependent). But UTC timezone can be casted.
• Python -> Localtime. On my CET machine.

So to get this consistent in UTC we would need:

class TimestampNowUTC(Default):
    @property
    def postgres(self):
        return "current_timestamp::timestamp at time zone 'UTC'"

    @property
    def sqlite(self):
        return "current_timestamp"

    def python(self):
        return datetime.datetime.now(datetime.timezone.utc)

I have renamed the above version to TimestampNowUTC.

Now for localtime it would be:

class TimestampNow(Default):
    @property
    def postgres(self):
        return "localtimestamp"

    @property
    def sqlite(self):
        return "select datetime(current_timestamp, 'localtime');

    def python(self):
        return datetime.datetime.now()

So what would be best, having both classes or keeping TimestampNow only. And then make sure that it always does localtime or always UTC.
This needs a bit more testing on various machines running in at least two different timezones.

[dantownsend]

You're right - this needs fixing. Timezones melt my brain, which is why I haven't tried tackling it sooner.

Having two separate classes could work.