Can't set timezone-naive datetimes

[Dnomin]

Describe the bug Can't set timezone-naive datetimes

To Reproduce

1. Model with two DatetimeFields and type sqlalchemy.DateTime(timezone=False)
2. Save timezone-naive datetime to first field (All ok)
3. Save timezone-naive datetime to second field (Error, can't compare aware and naive dts)

values from backends/base/executor.py:execute_update:

• from first execute: [datetime.datetime(2021, 2, 2, 0, 0), None]
• from second: [datetime.datetime(2021, 2, 2, 0, 0, tzinfo=<UTC>), datetime.datetime(2021, 3, 17, 14, 38, 11)]

So, as we can see, on second execute, first field value have UTC timezone

Expected behavior Timezone-naive fields

Additional context PostgreSQL : latest Tortoise-orm : latest Alembic with SQLAlchemy (for migrations) : latest

[lsabi]

Can you be more explicit? It seems strange, but maybe because I'm not understanding the context.

In particular, do you mind posting the model's structure? And explain what you mean with second execute? Is it an update query of the same record or a new record?

Thanks

[xSandie]

We meet the same strange problem. So I have to roll back to 0.15.18，there is no problem with this version.

[xSandie]

I have an old project using the version 0.15.18, when I upgrade to the latest version 0.16.18, all code with tortoise.fields.DatetimeField(auto_now_add=True) have the error tortoise.exceptions.OperationalError: invalid input for query argument $1: datetime.datetime(2020, 6, 6, 7, 12, 30,... (can't subtract offset-naive and offset-aware datetimes)

Possible causes

When I read your code, I found that the class tortoise.fields.DatetimeField now using SQL_TYPE = "TIMESTAMPTZ" in postgresql source code, it will cause some problem with the old project. I will appreciate that if your team could mention this in the changelog.

[lsabi]

You have to change the configuration during initialization of tortoise.

Here you see a sample config, though it's without the timezone related configuration, but we'll get there in a moment.

https://tortoise-orm.readthedocs.io/en/latest/databases.html#passing-in-custom-ssl-certificates

So you have to tune the configuration object mentioned above to get the datetime to work properly. There are two settings to be tuned: use_tz and timezone. You can read more here https://tortoise-orm.readthedocs.io/en/latest/timezone.html?highlight=timezone#introduction.

Here's a sample configuration that you could use, but I didn't try as I only used timezones:

{
        "connections": {
            "default": create_dsn(settings)
        },
        "apps": {
            "models": {
                "models": models_list,
                "default_connection": "default"
            }
        },
        "use_tz": False,
        "timezone": "UTC"
    }

[xSandie]

"use_tz": False,
    "timezone": "UTC"

I tried. It didn't work with postgresql, It seems that I need to change the code in my project 😢

[lsabi]

Have you dumped the schema/database before running it?

Also, I'm not sure about the config parameters you have to use, as I only used the "use_tz": True since the 0.16.18 release. Try setting it to True as well or not passing timezone in the configuration, maybe it helps...

[rafalstapinski]

Having the same issue,

tl;dr is that the column schema in postgres is timezone without time zone but tortoise's auto create and auto update timestamp functions are always datetime aware

The docs in tortoise orm state

When set use_tz = True, tortoise will always store UTC time in database no matter what timezone set

and tortoise achieves this with setting its "now" on auto create and auto update timestamps to be

def now() -> datetime:
    """
    Return an aware datetime.datetime, depending on use_tz and timezone.
    """
    if get_use_tz():
        return datetime.now(tz=pytz.utc)
    else:
        return datetime.now(get_default_timezone())

As you can see, both of these create timezone-aware datetimes. Because these are timezone aware (despite being UTC), they can not be compared to the database timezone-naive (timestamp without time zone in psql) at the adapter (asyncpg) level.

My practice (and generally as well, to my knowledge) is to store UTC timestamps in postgres but keep them timezone naive. Tortoise's implementation has changed to keep all postgres columns TIMESTAMPTZ or timestamp with time zone which clashes with the underlying column.

One option here is to change all your created and updated columns in your database to be timezone aware at UTC.

Personally, I'm going to run a fork of this going forward with the change that all my timezone columns will have the SQL_TYPE of timestamp without time zone and will strictly use datetime.utcnow()... like the description says in the first place....

class DatetimeField(Field, datetime.datetime):
    """
    Datetime field.

    ``auto_now`` and ``auto_now_add`` is exclusive.
    You can opt to set neither or only ONE of them.

    ``auto_now`` (bool):
        Always set to ``datetime.utcnow()`` on save.
    ``auto_now_add`` (bool):
        Set to ``datetime.utcnow()`` on first save only.
    """

What I hope tortoise-orm adopts, however, is for USE_TZ to actually do what its name implies in that

• use_tz will tell you whether your datetimes are timezone aware
• if true
  • use the timezone specified by passing in timezone
• if false
  • create datetime columns without a time zone (e.g. timestamp without time zone for postgres)
  • have the tortoise.timestamp.now() function return `datetime.utcnow(), a timezone naive datetime

[rafalstapinski]

https://github.com/rafalstapinski/tortoise-orm/ is the fork i'm currently using with the changes I outlined at the end of my previous comment.

[weni09]

when setting use_tz = True,it can transform value of 'value' to a value with '+0.00' by using method 'timezone.make_aware(value, "UTC")',but this making store of datetime to be failed on mysql(other database have not be tested)!

[Achilles0509]

Faced the same issue. I tried with use_tz=True and use_tz=False options, both are failed.

[dyuzhou]

timezone='Asia/Shanghai' ？ On PostgreSQL, it seems that no matter how you set it, it always has UTC time

import datetime
from tortoise import timezone

print(datetime.datetime.now())
# > 2023-06-16 15:17:52.114416

print(timezone.now())
# > 2023-06-16 07:17:52.114416+00:00

print(timezone.get_default_timezone())
# > UTC

[cikay]

looks like it is not going to be solved. So ovveride save method to set update and create fields

from datetime import datetime
from typing import Iterable, Optional

from tortoise.fields import DatetimeField
from tortoise.models import Model
from tortoise.backends.base.client import BaseDBAsyncClient


class BaseModel(Model):
    created_at = DatetimeField(editable=True)
    updated_at = DatetimeField()

    def save(
        self,
        using_db: Optional[BaseDBAsyncClient] = None,
        update_fields: Optional[Iterable[str]] = None,
        force_create: bool = False,
        force_update: bool = False,
    ):
        if not self.id:
            self.created_at = datetime.now()
        self.updated_at = datetime.now()
        return super().save(using_db, update_fields, force_create, force_update)