CODEWITHSUNDEEP
postgresqlsequelize.js
Luca Guarro
Luca Guarro

Reputation: 1168

Time is being stored in postgres database without timezones even though I provide them to sequelize

I am populating a table in my postgres database using sequelize. I have a field with a Time data type but the time zones are not getting stored. I am doing a bulkCreate like so:

await models.Event.bulkCreate(events);

where events are the new instances I am adding formatted in JSON. Here is an example of one of the instances:

    {
        "_REMOVE_INFO": "App Development - Alessandro Ferrari",
        "City": "Milano",
        "Region": "MI",
        "Country": "Italy",
        "Address": "Via Massimo D'Azeglio, 3, 20154",
        "Date": "2020-11-21",
        "Time": "9:00 AM PST",
        "Title": "Code your idea ",
        "Pic": "https://images.pexels.com/photos/546819/pexels-photo-546819.jpeg?auto=compress&cs=tinysrgb&dpr=2&h=650&w=940",
        "Description": "You'll learn the basic building blocks of a website and how to style them. No previous coding experience needed.You will leave with the core concepts of HTML (tags, elements, and attributes), and the basics of CSS (selectors, properties, and values) and some Javascript. All your work will be in a platform that will allow you to code along with your instructor, and continue to work on it after you leave!",
        "MaxCapacity": 20,
        "Type": "Workshop"
    }

But after I run the script, postgres is not storing the timezone portion of the data. In fact it explicitly says "time without time zone" as can be seen in this screenshot:

enter image description here

In my Event model schema, I define Time as the following

    Time: {
        type: DataTypes.TIME,
        allowNull: false,
    }

Does my issue reside in the way I am formatting the JSON or in my configuration of Sequelize? How can I resolve it?

Upvotes: 1

Views: 1115

Answers (2)

Schwern
Schwern

Reputation: 164919

time without time zone is a Postgres data type. If you want to store time with time zone or timetz you have to change the column to that data type.

But don't. If you need to store times with time zones, use timestamp with time zone or timestamptz. In Sequelize this is .DATE.

The Postgres docs explain why to avoid time with zone.

Although the date type cannot have an associated time zone, the time type can. Time zones in the real world have little meaning unless associated with a date as well as a time, since the offset can vary through the year with daylight-saving time boundaries.

The default time zone is specified as a constant numeric offset from UTC. It is therefore impossible to adapt to daylight-saving time when doing date/time arithmetic across DST boundaries.

To address these difficulties, we recommend using date/time types that contain both date and time when using time zones. We do not recommend using the type time with time zone (though it is supported by PostgreSQL for legacy applications and for compliance with the SQL standard). PostgreSQL assumes your local time zone for any type containing only date or time.

For example, where I am it's -0700. I want to store that lunch time is at noon. Using a timetz, lunch is at 12:00:00-0700. In a few months daylight savings time will end and we'll "fall back" an hour to -0800. But lunch is still at 12:00:00-0700 which is now 1pm local time.

If instead I store lunch time as a time, 12:00 will be correct no matter the time zone.

Upvotes: 2

Jim Macaulay
Jim Macaulay

Reputation: 5141

You have to use with time zone in your data type **timestamp [ (p) ] with time zone ** If required alter your table,

alter table table_name alter column column_name TIME with time zone;

Upvotes: 0

Related Questions