Date / Timestamp and Timezone issue with Sequelize

[Simon_BRAMI1]

Hi everyone,

I’m working with Postgres/Sequelize and I’m facing a strange issue/behavior that I’ve been able to fix but that still remains strange.

My Postgres DB stores datetimes as ‘timestamps’, whenever I want to save a new date and time to my db through a SmartAction or SmartField, I do something like this:

myTable.lastUpdate = new Date()
await myTable.save()

Being based in France, I expect my date to be saved and displayed correctly on the front end with +02:00 timezone (Europe/paris for instance).

But every date is saved by default as +00:00 to my DB, so my dates always miss 2 hours.

I tried to set up the correct timezone on the project settings or let the browser default, but the issue remains.

My ultimate workaround was to add to config/database.js the following config:

const databaseOptions = {
  logging: !process.env.NODE_ENV || process.env.NODE_ENV === 'development' ? console.log : false,
  pool: { maxConnections: 10, minConnections: 1 },
  dialectOptions: {},
  timezone:"+02:00" // here
};

By default, Sequelize set this to +00:00 as mentioned here: Sequelize | Sequelize

Once I do that modification, my date now saves correctly to my DB, and the timezone in the project settings now automatically sets the date to the corresponding selected timezone or browser’s default.

Have you ever seen such strange behavior?

I feel like I shouldn’t use that timezone option to tell my DB how to correctly save dates, it should instead use UTC and display the correct date based on your timezone

Let me know what you think!

Simon

[Guillaume_Cisco]

Hello @Simon_BRAMI1,

Indeed, this should not happen.
Are you sure the date stored in db transformed to a +00:00 timezone (UTC) also has its hours modified? This should be the case.
It is better that all your datetimes are stored in UTC format in your database, then the web application will display them in the correct timezone for the user.

Could you tell me what versions of postgresql/sequelize.forest-express-sequelize you are using please?

[Simon_BRAMI1]

@Guillaume_Cisco

Could you tell me what versions of postgresql/sequelize.forest-express-sequelize you are using please?

I’m using Postgres 10.5 and forest-express-sequelize 7.11.3.

    "axios": "^0.21.1",
    "body-parser": "1.19.0",
    "chalk": "~1.1.3",
    "cookie-parser": "1.4.4",
    "cors": "2.8.5",
    "debug": "~4.0.1",
    "dotenv": "~6.1.0",
    "express": "~4.17.1",
    "express-jwt": "6.0.0",
    "forest-express-sequelize": "7.11.3",
    "morgan": "1.9.1",
    "pg": "~8.2.2",
    "require-all": "^3.0.0",
    "sequelize": "~5.15.1",
    "uuidv4": "^6.2.10"

@Guillaume_Cisco

Indeed, this should not happen.
Are you sure the date stored in db transformed to a +00:00 timezone (UTC) also has its hours modified?

Well, when I tried to store let’s say: 1/1/2021 16:00 (french hour) it would save it in the DB as:
1/1/2021 14:00 which is supposed to be correct as we want UTC. But on my dashboard, it would appear as 1/1/2021 14:00 and not 1/1/2021 16:00.

Now I added '+02:00', it is saved in the DB as 1/1/2021 16:00 and the conversion on the dashboard is correct based on the timezone selected.

[Guillaume_Cisco]

The storing is correct.
I think you may have an extra option on your timezone configuration.
Could you check on your project settings on the Forest Admin web app.
And in the configuration you pass to your lianas please?

[Simon_BRAMI1]

const chalk = require('chalk');
const path = require('path');
const Liana = require('forest-express-sequelize');
const { objectMapping, connections } = require('../models');

module.exports = async function forestadmin(app) {
  app.use(await Liana.init({
    configDir: path.join(__dirname, '../forest'),
    envSecret: process.env.FOREST_ENV_SECRET,
    authSecret: process.env.FOREST_AUTH_SECRET,
    objectMapping,
    connections,
  }));

  console.log(chalk.cyan('Your admin panel is available here: https://app.forestadmin.com/projects'));
};

Is it that Liana config you’re looking to see?

I also checked my DB settings:

show timezone;

and it outputs UTC.

[Guillaume_Cisco]

I will test this right away, and let you know if I’m able to reproduce

[Simon_BRAMI1]

Here is the docker image I use for postgres if it can help

version: '3.7'
services:
    postgres:
        image: postgres:10.5
        restart: always
        environment: 
          - POSTGRES_USER=<input>
          - POSTGRES_PASSWORD=<input>
          - POSTGRES_DB=<input>
        logging:
          options:
            max-size: 10m
            max-file: "3"
        ports:
          - '5438:5432'
        volumes:
          - ./postgres-data:/var/lib/postgresql/data

My timestamps are “timestamp without time zone”, idk if it has an impact since it’s supposed to be all UTC.

[Guillaume_Cisco]

@Simon_BRAMI1

timestamp without time zone

I think we found the culprit here. If the stored data has no timezone, then it looks like it is not working.
Could try with a column whom type is timestamp with time zone?

Thank you,

[Simon_BRAMI1]

Will try this right away.

[Guillaume_Cisco]

I created three columns L id, created_at and updated_at.

As you can see, I put the same value for each timezone. But created_at is timezone aware.

On the web application, only the timezone aware will be displayed in the user timezone.

Hope it helps

[Simon_BRAMI1]

Just did the same, indeed it works!

Thank you for your assistance on this Guillaume

Moral of the story: When you generate a DB code first, make sure your date types are the good ones