Sequelize: How to configure column without timezone in timestamp?

[luccasmaso]

For default, this option:

updated_at: { type: DataTypes.DATE }

This will create a column timestamp with timezone. But I need this to be without timezone.
Is it possible?

[janmeier]

Why do you need it to be without timezone? You can set the timezone to something other than UTC if that's the problem

Sequelize purposedly uses with timestamps to make sure everything works correctly across timezones

[janmeier]

Closing, no response

[gpedro]

@janmeier, i've same problem. How i can do this?

[janmeier]

@gpedro Then I'll ask you the same question - why do you need this?

[gpedro]

@janmeier because i work with different time zones and need store this without timezone to render with local timezone

[janmeier]

But no matter what, the time you are storing still represents a single point in time - to be able to identify that you need to store the timezone (or store it as a unix timestamp).

The date is returned as a js string, and will still be shown to the user in his local timezone

[gpedro]

aaaaand is a legacy system :/

[janmeier]

In case of a legacy system you don't need to worry about what Sequelize.DATE does, since the table is already created, right?

In case you need to disable parsing of date columns into a JS date object, have a look at https://github.com/sequelize/sequelize/issues/3000

[gpedro]

right
great!
thanks for supporting me :smile:

[arieljake]

@janmeier are you still around? Question for you:

You said:

Why do you need it to be without timezone?

A birthdate should not have a timezone right? No matter where in the world a user logs in, when we display their birthdate, it should always be the same. How do we ensure this?

[janmeier]

@arieljake Perhaps there is a bit of confusion about .DATE - Sequelize.DATE is represented as a DATETIME in the database - that is a date and time part, which inherently always has a timezone, because they represent a fixed point in time.

To get only the date part, use Sequelize.DATEONLY - I know its a bit confusing, but it's for backwards compatibility.

I don't remember if we try to parse a DATEONLY as a javascript date object, but if we do, I'd consider that an error :)

[arieljake]

@janmeier So having switched my column to DATEONLY I see it stored in Postgres as "YYYY-MM-DD" as opposed to a timestamp (GOOD) but it is returned to my client as a full timestamp (BAD) so I would assume Yes, the code is parsing DATEONLY as a javascript date object.

[janmeier]

@arieljake Alright, I would think thats a bug, since representing a date as something with a time part (which javascript dates will always have) is wrong in my POW - could you please open a new issue, then we can continue the discussion there.

Should be pretty easy to fix with the new datatypes parsing system, but we need to discuss whether its a BC break

[KisukPark]

@janmeier Is the DATEONLY parsing problem fixed?? I'm using version '3.23.6'.

[janmeier]

https://github.com/sequelize/sequelize/issues/4858

[vihuarar]

I'm also having problems with dateonly...
I defined I column in the model
fecha_compra: {
type: DataTypes.DATEONLY,
allowNull: true
},

but it is returned as "2017-02-15T00:00:00.000Z"

I need to return only the 2017-02-15 part...

In the database I have defined the field as "date"

[garrett-thompson]

I need a Timestamp without time zone to run a function like this on it (someTimestamp - interval '5 minutes') which doesn't work if it has time zone

[vihuarar]

I found a partial solution...

I convert the field when the model return it... using the get attribute

fecha_compra: {
  type: DataTypes.DATEONLY,
  allowNull: true,
  get      : function()  {
    // 'this' allows you to access attributes of the instance
    if(this.getDataValue('fecha_compra')){
      return this.getDataValue('fecha_compra').toISOString().slice(0,10);
    }
  },
},

[andythecoderman]

Was this ever actually solved? In my specific case the winston postgres appender only works if it's a timezone with no timestamp type column, and I can't just let the appender create the table as I need to set message to be a text type instead of a varchar.

[janguianof]

@vieiralucas it is so frustrating, I'm dealing with the same problem right now

[standy]

Why do you need it to be without timezone?

Imagine calendar reminder for next week 9 am. Need to store events in future, which happens in local time and I don't even know where is it, so i cant apply any timezone. Good reason for timestamp without time zone to me.

[ivpal]

I need it too.

[sigod]

It's very sad, that Sequelize doesn't support this.

[akshayeshenoi]

This is quite frankly unbelievable. Is there any resolution on the cards?
So many issues yet no action..

3000

7503

5002

What is a reasonable argument for not providing the user the ability to turn off parsing dates?

[tsirolnik]

If so many users need this feature then why not just provide it?

[normancarcamo]

I'm still struggling with this right now again My gosh!!

[abelosorio]

Hi everyone!

I think the question is not (quoting https://github.com/sequelize/sequelize/issues/2572#issuecomment-62952607):

Why do you need it to be without timezone?

It is needed. Period.

PostgreSQL [1] (and other RDMS [2]) supports this feature and there are plenty of reasons to use it, so Sequelize must support it.

@janmeier please can you please review this issue one more time? If it's OK to you I can create a PR including the new type for TIME WITHOUT TIMEZONE and TIMESTAMP WITHOUT TIMEZONE (maybe TIME_WITHOUT_TZ and DATE_WITHOUT_TZ?) for PostgreSQL.

Thanks for your time.

cc @sergioshev

[normancarcamo]

@abelosorio or STRING_DATE, STRING_DATETIME, DATE_RAW, DATETIME_RAW, I touched the core and the actions of the Model and it can have workarounds.

They say that we can set the option of “raw” enable but it’s not much recommended, nevertheless I think that the option must not be touched if there exists at least options like we’ve proposed in the past.

Anyway, I think that playing with the prototype and/or setting the option “raw” enabled could help.

[abelosorio]

Thanks @normancarcamo. Yes, there are some workarounds but this issue was open 4 years ago... so I think it's time for a final and real solution...

[normancarcamo]

@abelosorio And I think they will omit this because I read another article in which a member said that "working with date types is really hard" and they converts the dates to avoid problems, I think what he said is true when working with dates, o I agree with him in part.

And by the way the v5 is in beta right now and there's no anything related to it, so maybe this will never happen.

I think that we need to experiment creating our own solutions, test them and publish them as packages in npm to have a bit of feedback and then request to this people if they can add the ideas to the project to avoid this anymore.

This is the changelog of the v5 if is someone else here wants to look it.
https://github.com/sequelize/sequelize/blob/master/docs/upgrade-to-v5.md

[abelosorio]

Yes @normancarcamo, this seems that will be kept aside for a long long time...

I will work on this today and try to come up with a wrapper for DataTypes and Sequelize (for migrations) using Sequelize v4.

I'll try to create one npm package for each type I need in PostgreSQL: INTERVAL (#4900), TIME WITHOUT TIMEZONE, TIMESTAMP WITHOUT TIMEZONE; and one npm package that adds all of them at once...

Something like:

model definition:

const withInterval = require('sequelize-interval-postgres');

module.exports = function (sequelize, SequelizeDataTypes) {
  const DataTypes = withInterval(SequelizeDataTypes);

  const MyModel = sequelize.define('myModel', {
    someIntervalField: {
      type: DataTypes.INTERVAL
    },

    // ...
  });

  // ...

  return MyModel;
};

migration:

const withInterval = require('sequelize-interval-postgres');

module.exports = {
  up: function (queryInterface, SequelizeBase) {
    const Sequelize = withInterval(SequelizeBase);

    return queryInterface.addColumn('myModel', 'someIntervalField', {
      type: Sequelize.INTERVAL
    });
  },

  // ...
};

Would it work for you?

UPDATE

package ready to use :point_right: https://www.npmjs.com/package/sequelize-interval-postgres

[normancarcamo]

@abelosorio nice to see that you have worked on it.
In my case it doesn't cover my requirement, so I will need something different, what I just need is to disable the convertion of the Date Types, I need them in raw string pass the values directly to my queries in the datasources, to give you some examples:

The apps are sending different formats of Date in string type due to they weren't built by us, they were developed in the past (legacy code tbh) by other companies, so our target is to connect those datasources and get data from them using our system (we act as a centralized layer), as well those data sources needs the raw format to get results because it internally compares date in raw, this are some examples:

2012-10-03
2012/11/12
2018-01-01 10:00:00

then they are used with a between clause (datasource #1 mysql):

SELECT id, tenant_id, visitor
FROM visits
WHERE updated_at BETWEEN '2018-01-01 10:00:00' AND '2018-01-01 10:00:00'

(hope you got my point).

Now i will see when I got some time and mood to do my part.

[abelosorio]

Other possible workarounds:

• https://www.npmjs.com/package/sequelize-date-no-tz-postgres
• https://www.npmjs.com/package/sequelize-time-no-tz-postgres

[dharijanto]

I totally understand the frustration here, but I don't think ranting here helps. It would have been much more productive if you guys who need it try to make a pull-request and get it submitted. Remember we're already have the luxury of using sequelize for free..

Anyway, for me personally, I solved the problem by storing the date as Sequelize.String instead. When I need to parse it, I just use moment.js, like moment('2018-08-08') this works really well

[normancarcamo]

@nusantara-cloud yes its true, I got your point, but I think that sometimes a solution to this might not fit to all people requirements, nevertheless its workaroundable.
Cheers.

[abelosorio]

Hi @nusantara-cloud! I totally agree with you. I've been waiting for at least feedback from Sequelize's mantainers so I can send a PR with a solution, but I haven't received any response yet. So I decided to share my workarounds as npm packages (sequelize-replace-enum-postgres, sequelize-interval-postgres, sequelize-time-no-tz-postgres, sequelize-date-no-tz-postgres, sequelize-views-support), easy to install and easy to use.

However, I coded these packages without tests (I didn't want to spend more time without feedback) and using PostgreSQL, so not sure if it would work with other DB engines.

My idea is that if those packages work as expected, and I get good feedback from them, I'd write a PR copying the code from those packages.

Last but not least, I've been using all these packages in my projects and worked as I expect.

Sounds good?

[dharijanto]

@abelosorio that sounds really good. Open source community needs more ppl like you :)

Have you tried emailing the maintainer, btw? Would be nice to get the features merged instead of as a separate package.

[abelosorio]

Thank you man. I didn't email maintainers since I was waiting to get more feedback from other users... I don't even know if those packages would work with other DB engines, etc...

I'd really like to have more feedback, write tests, documentation, etc... But right know I don't have too much time to work in that.

[lichenhao]

// using custom type for without time zone and define your custom getter for format
{
  type: 'TIMESTAMP WITHOUT TIME ZONE',
  get () { return moment(val).format('YYYY-MM-DD HH:mm:ss ZZ')}
}

### very important in postgre: set your type parser 1114(oid for timestamp) do not try to translate new Date.
code here: pg.types.setTypeParser(1114, str => str)
ref: how to working with postgresql timestamp without timezone in node

[TakanashiOuken]

We still don't have a solution for this?

[daviddelusenet]

I'm now also running into this easy :(

[papb]

Hi everyone, is this really still an issue? Doesn't DataTypes.DATEONLY solve the problem?

[qadratic]

Hi everyone, is this really still an issue? Doesn't DataTypes.DATEONLY solve the problem?

DATEONLY has only DATE. I have exactly same requirements as

@abelosorio nice to see that you have worked on it.
In my case it doesn't cover my requirement, so I will need something different, what I just need is to disable the convertion of the Date Types, I need them in raw string pass the values directly to my queries in the datasources, to give you some examples:

The apps are sending different formats of Date in string type due to they weren't built by us, they were developed in the past (legacy code tbh) by other companies, so our target is to connect those datasources and get data from them using our system (we act as a centralized layer), as well those data sources needs the raw format to get results because it internally compares date in raw, this are some examples:

2012-10-03
2012/11/12
2018-01-01 10:00:00

then they are used with a between clause (datasource #1 mysql):

SELECT id, tenant_id, visitor
FROM visits
WHERE updated_at BETWEEN '2018-01-01 10:00:00' AND '2018-01-01 10:00:00'

(hope you got my point).

Now i will see when I got some time and mood to do my part.

[sanket-work]

I want 2017-05-22 19:46:12.00 but sequelize returns this 2017-05-22T19:46:12.000Z with timezone
Has anyone done this ?

[sanket-work]

This is severe issue and I am amazed how sequelize is ignoring this :-1:
Here is a doc from AWS where they say the timezone at DB level should always be in UTC and any local timezone changes should be handled at application layer.

https://aws.amazon.com/premiumsupport/knowledge-center/rds-change-time-zone/

[jbdevelop]

I was trying to insert data...

I solved it like this

await TransferDevice.create({
          device_id: item.device_id,
          transfer_id: item.transfer_id,
          transfer_route_id: item.transfer_route_id,
          start_date: null,
          end_date: null,
          last_update: null,
          tracking_token: null,
          created_at: Sequelize.literal(`NOW() - INTERVAL '3h'`),
          updated_at: Sequelize.literal(`NOW() - INTERVAL '3h'`),
        })

https://stackoverflow.com/questions/43634819/how-to-use-interval-in-sequelizejs

[max-lt]

This is how I solved it in typescript

import { DateDataTypeConstructor } from 'sequelize';
import { DataTypes, Utils } from 'sequelize';
import { inherits } from 'util';

// tslint:disable-next-line: no-shadowed-variable
DataTypes['TIMESTAMP'] = function TIMESTAMP() { };

inherits(DataTypes['TIMESTAMP'], DataTypes.DATE);

export const TIMESTAMP = Utils.classToInvokable(DataTypes['TIMESTAMP'] as DateDataTypeConstructor);

TIMESTAMP.prototype.key = 'TIMESTAMP';

TIMESTAMP.prototype.toSql = function toSql(options) {
  return 'TIMESTAMP';
};

[mabsystems]

I faced the same problem and helped me the next:

const { utcToZonedTime } = require('date-fns-tz')
const { format } = require('date-fns')

Tickets.init({
...
createdAt: {
            type: DataTypes.DATE,
            get() {
                return format(utcToZonedTime(this.getDataValue('createdAt'), 'Asia/Atyrau'), 'dd.MM.yyyy HH:mm')
            }
        }
...