How to query Postgres datetime without timezone?

[myflightconnect]

Hello,

I have a Postgres database with a timestamp field set with " WITHOUT TIME ZONE" where I store datetime in UTC.

If I query my table directly on the command line, I get the good date.

But I can’t get this value with ForestAdmin/Sequelize

The problem, it’s that it is not the same date at the end…

I’ve checked GitHub - abelosorio/sequelize-date-no-tz-postgres: Add support for DATE (timestamp without time zone) data-type for PostgreSQL in Sequelize , How to configure column without timezone in timestamp? · Issue #2572 · sequelize/sequelize · GitHub

without much success.

Another option for me would be to be able to run a query with Sequelize without the “serializer” step. Just raw data.

I’ve tried “raw”, “plain” but the datetime are still serialized and returned as javascript date objects…

I start to run out of options…

How can I get a datetime without timezone manipulation from a Postgres database?

Context

• Project name: myflightconnect
• Agent (forest package) name & version: forest-express-sequelize@9.2.9
• Database type: Postgres

[nbouliol]

Hello @myflightconnect

Welcome on the forest community, and thank you for posting

What is you final goal ?

You can customize the date format of your trip event field in the collection settings

In your Quote model, the trip event type seems to be DataType.STRING, maybe you should try to switch to DataType.DATE or DataType.DATEONLY (see Manual | Sequelize)

[myflightconnect]

I have a “quote” table on which I want to add a field to show related info. The related info comes from a “trip_step_event” table (the “event_date_time” field).

So, I’ve created a SmartField for my quote collection where I fetch “event_date_time” from “trip_step_event”.

In Postgres, the “event_date_time” field is defined as “Timestamp without timezone”. This is important because I’m dealing with Flight event. In this world, you speak in UTC no matter where you are.

That means, my “event_date_time” datetimes are stored without timezone info.

Postgres knows it’s a datetime in UTC. But when sequelize reads the “event_date_time” value, it can’t find a timezone, so it adds a timezone to the date instead of considering the lack of timezone means UTC https://github.com/sequelize/sequelize/blob/29901187d9560e7d51ae1f9b5f411cf0c5d8994a/lib/data-types.js#L471-L475

Basically, it takes the date without timezone and considere that the date is in the current/server timezone.

So for my example, I’m in France. ForestAdmin runs in France, so it takes my UTC datetime “2023-08-16T00:00:00” and it parses it like if it was in Central European Summer Time with 2 hours ahead of UTC. So when I ask to return a UTC string for my date, it returns “2023-08-15T22:00:00”.

What I need, is that it takes my UTC datetime in UTC…

Some examples in the javascript browser of the difference between parsing a datetime using the local timezone, or parsing a datetime considering it is in UTC.

I’ve tried to change the model type to “DataTypes.STRING” but Sequelize still parse the value as a javascript date object.

It’s an issue with Sequelize https://github.com/sequelize/sequelize/issues/2572 … But I was wondering if somebody had any workaround.

---

On my side, I’ve ended up writing a SQL query where I ask Postgres to return the date as a string to avoid Sequelize to parse the date…

With the json postgres function, the date is returned in string using ISO format.

[nbouliol]

Hello @myflightconnect

Thank you for this explanation, sorry I dont have any way to solve this.

One of tricks I saw was to override the sequelize model getter with something like

date: {
        type: DataTypes.DATE,
        get: function () {
          return moment(this.getDataValue("date")).format(
            "YYYY-MM-DD HH:mm:ss"
          );
        },
      },

If you keep the raw query approach, I encourage you to use sequelize replacements