TimestampNow doesn't use UTC timestamp

[abhishek-compro]

piccolo/piccolo/columns/defaults/timestamp.py

Line 64 in 97715b8

return datetime.datetime.now()

A better approach would be to:

return datetime.datetime.now(tz=datetime.timezone.utc).replace(tzinfo=None)

[dantownsend]

Interesting point. It's tricky with Timestamp because it's timezone naive to know what the user really wants. If they want it in their local timezone, or in UTC. In the database we use current_timestamp::TIMESTAMP.

[sinisaos]

I don't think we need this change because Timestamp is a naive datetime column (no timezone information). For timezone aware datetime we should use Timestamptz. Sorry if I'm wrong.

[dantownsend]

It's just because datetime.datetime.now() uses the timezone of the server. So even though it doesn't store timezone information on the object, if the server uses the Paris timezone, it will be the current time in Paris.

It's something which doesn't affect me much, because the server is usually set to UTC, and my local timezone on my development machine is GMT, which is basically the same. But I understand how it could be a problem.

This datetime.datetime.now(tz=datetime.timezone.utc).replace(tzinfo=None) gives the time in UTC without timezone information, rather than the local time.

On the database column we use current_timestamp to generate the default value, if someone bypasses the ORM. There's also localtimestamp which I guess we could use:

https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

I'm not 100% sure what to do. I personally only use Timestamptz because there's no ambiguity - everything is stored in UTC. With Timestamp columns, the intentions of the user are less clear. Do they want it to default to the current UTC timestamp? Or their local timestamp?

We could have an argument which can be passed to TimestampDefault.

my_column = Timestamp(default=TimestampDefault(tz=datetime.timezone.utc))

Which determines which timezone the default value is generated in (even though we discard the timezone information when saving it).

It's a tricky one.

[dantownsend]

Or we have two different classes TimestampDefault and TimestampUTCDefault. We could leave TimestampDefault as is.

[sinisaos]

It's just because datetime.datetime.now() uses the timezone of the server. So even though it doesn't store timezone information on the object, if the server uses the Paris timezone, it will be the current time in Paris.

Exactly. datetime.datetime.now() returns a naive datetime representing the server local time, which is, I think, appropriate for naive datetime columns like Timestamp. Fortimezone-aware datetime we should always use the Timestamptz column type.

datetime.datetime.now(datetime.timezone.utc).replace(tzinfo=None) is misleading because it creates a timezone-aware datetime and then strips the timezone information, resulting in a naive datetime which still represents UTC time. I'm sorry if I'm wrong, but I don't think we should change anything.

[dantownsend]

Yeah, I see what you're saying. We could have something like this:

class TimestampNowUTC(Default):
    """
    The current timestamp, in UTC.
    """

    @property
    def postgres(self):
        return "(current_timestamp AT TIME ZONE 'utc')"

    @property
    def cockroach(self):
        return "(current_timestamp AT TIME ZONE 'utc')::TIMESTAMP"

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

    def python(self):
        return datetime.datetime.now(tz=datetime.timezone.utc).replace(
            tzinfo=None
        )

It turns a Timestamp column into a pseudo Timestamptz column, with all values in UTC. But I think it's best to just use Timestamptz. I'm going to add something to do the docs recommending Timestamptz over Timestamp.

[sinisaos]

I'm closing because it won't be implemented. The documentation has been changed to clarify the timestamp columns in #1370.

[dantownsend]

Thanks @abhishek-compro for raising this - it did give us plenty to think through. We should make it easier to subclass default classes, so users can customise to their liking.

[abhishek-compro]

I don't understand why do we need to save timestamps with local timezone into consideration when we can't store the timezone. Its generally recommended by almost everybody to only use UTC timestamps especially at the db level when the storage type doesn't allow timezone information.

In my opinion we shouldn't allow application (here postgres and python) defaults. In my case the server is IST but the database is UTC which caused corrupted timestamp values to be stored automatically which I can't revert back because the same database is being used by different ORMs and applications.

[sinisaos]

@abhishek-compro I think in your case your solution would be perfectly valid, but maybe someone else needs a naive local datetime. Sorry if I'm wrong, but if you can't use Timestamptz, have you tried adding your solution to the column definition? Something like this

import datetime
from piccolo.columns import Timestamp, Timestamptz
from piccolo.table import Table


def custom_timestamp():
    return datetime.datetime.now(tz=datetime.timezone.utc).replace(tzinfo=None)


class Task(Table):
    naive_local = Timestamp()  # default
    naive_utc = Timestamp(default=custom_timestamp)  # your need
    timezone_aware = Timestamptz()

Result is (I think is correct)

{
    "id": 1,
    "naive_local": datetime.datetime(2026, 3, 10, 6, 4, 7), # UTC+01:00 - my local machine (CET)
    "naive_utc": datetime.datetime(2026, 3, 10, 5, 4, 7), # UTC - what you need
    "timezone_aware": datetime.datetime(
        2026, 3, 10, 5, 4, 7, tzinfo=datetime.timezone.utc # UTC + timezone
    ),
}

Sorry if I misunderstood and missed your point.

[abhishek-compro]

Something like this

import datetime
from piccolo.columns import Timestamp, Timestamptz
from piccolo.table import Table


def custom_timestamp():
    return datetime.datetime.now(tz=datetime.timezone.utc).replace(tzinfo=None)


class Task(Table):
    naive_local = Timestamp()  # default
    naive_utc = Timestamp(default=custom_timestamp)  # your need
    timezone_aware = Timestamptz()

This solves the issue but the following is still unsolved:

In my opinion we shouldn't allow application (here postgres and python) defaults. In my case the server is IST but the database is UTC which caused corrupted timestamp values.

I am saying this because users of piccolo will mostly use defaults which will cause these issues. Fixing the main implementation would be more important where both python and postgres generate consistent timestamp values for any given type.

You are coming from a perspective where postgres and python are in the same timezone which is fine for applications directly deployed on the same server. For communication with multi-region applications, timezone is very important or use UTC to prevent any confusion. If each application keeps assuming their local time in absence of timezone then the timestamp keeps changing for each data transfer.