DATEONLY field is being parsed as a javascript Date with time/zone #4858

arieljake · 2015-11-13T13:48:59Z

I am using postgres.

My dateonly field is being stored as YYYY-MM-DD but when I pull that data down to the client, it is being sent as a full timestamp with time and timezone parts.

dongseok0 · 2016-02-02T06:10:44Z

Same in mysql too

denisazevedo · 2016-04-13T19:44:19Z

It's happening in Sequelize 3.21.0 with a date type in Postgres :

models/users.js:

dob: { 
  type: DataTypes.DATEONLY
}

routes/users.js:

User.create(user).then(function(result) {
  console.log('User created:', result.dob);
});

Postman response:

"dob": "1980-06-17T00:00:00.000Z",

console log:

User created: Mon Jun 16 1980 21:00:00 GMT-0300 (Hora oficial do Brasil)

Postgres select:

"1980-06-17"

lahvey · 2016-06-22T05:47:44Z

+1
input is "date": "2016-06-21T16:00:00.000Z" which should be Wed Jun 22 2016 00:00:00 GMT+0800 (CST) with timezone, but sequelize saved as 2016-06-21

janmeier · 2016-06-22T05:51:06Z

@lahvey DATEONLY saves only the date part - If you want the best time as well, use DATE

lahvey · 2016-06-22T06:02:50Z

So the date part should be the date string date part, no matter what the timezone is, right?
If the date string is "2016-06-22T00:00:00+0800", it will save as 2016-06-22.
If it is "2016-06-21T16:00:00.000Z", then it should be 2016-06-21

janmeier · 2016-06-22T07:00:03Z

Yes - I was confused by the mention of times, so I thought you were expecting the field to store timestamp as well as date.

reorg3 · 2016-07-25T23:52:40Z

Sequelize should return only date part for DATEONLY fields. I am using below workaround in my model definitions for all DATEONLY fields and it seems to be working fine so far:

    regDate: {
      type: DataTypes.DATEONLY,
      get: function() {
        return moment.utc(this.getDataValue('regDate')).format('YYYY-MM-DD');
      }
    }

felixfbecker · 2016-07-26T20:55:07Z

I am constantly facing issues with this aswell. DATEONLY is timezone-independent on the database side, it should only return the date part as a string. Date objects are not timezone-independent. If you insert a UTC Date object into a DATEONLY column, the timezone will be cut off. If you query that same date, it will be parsed in local timezone, and the date part will be one day off if you are +UTC.

The problem though is not in Sequelize. It is already in the drivers, for example postgres-types which is used by pg parses DATEONLY columns as Date objects.

Poitrin · 2016-08-12T07:34:49Z

I tried @mbhin's code snippet, but it returns me the previous day of the date stored in the database. The following function is working for me:

function getDateWithoutTime(date) {
    return require('moment')(date).format('YYYY-MM-DD');
}

palodequeso · 2016-08-26T15:08:24Z

But seriously, DATEONLY should not be parsed into a Date() object... orrrrr, at least can we just use {raw: true} to get the raw Date string? Right now, even raw is still parsing it into a Date() it seems. :(
Adding this large transform on get seems cumbersome for large data sets.

felixfbecker · 2016-08-26T15:15:13Z

@palodequeso Yes. Absolutely. I want to tackle this for 4.0 as it affects me and it's a breaking change. But it depends on driver implementation. Need to open an issue at postgres-date.

afucher · 2017-01-12T14:50:03Z

This will be avaiable in sequelize 4.0?

sushantdhiman · 2017-01-12T14:57:33Z

Yes, It will be. Its due in next release, hopefully end of this month.

shanmohod · 2017-03-06T14:40:09Z

+one

shanmohod · 2017-03-06T14:41:12Z

Any workaround to return date only, I tried @Poitrin way but its giving invalide date in date field

cupliz · 2017-06-05T02:35:42Z

this issue has been everywhere and existed from looong time ago...
please developers, at least give users freedom to get date output as we see in the database, pure output without adding timezone or bla bla in it

thanks @janmeier

felixfbecker · 2017-06-09T15:18:04Z

Could you elaborate what problem you have exactly? Please don't ping maintainers on random questions. We respond when we have time.

javiertury · 2018-08-29T18:59:01Z

DATEONLY ranges, defined as DataTypes.RANGE(DataTypes.DATEONLY), are still an array of Date objects with timezone information.

As per #9074, it seems that some type conversions are handled by sequelize and other by pg library. To parse DATEONLY ranges as string use the following workaround.

var Sequelize = require('sequelize');
var pg = require('pg');

// Override DATEONLY parser
pg.types.setTypeParser(1082,'text',function(text) {return text;});

var sequelize = new Sequelize(...);
...

EDIT: Found the root of the problem and the solution

Vaelek · 2019-04-22T17:01:37Z

And if we are not using Postgres?
I am simply trying to query a DATE field from a MSSQL db and get the DATE out of it.. instead I get a UTC date converted to a localized time which gives me the day before the one actually in the db.

andersonmendesdev · 2019-05-05T01:57:33Z

And if we are not using Postgres?
I am simply trying to query a DATE field from a MSSQL db and get the DATE out of it.. instead I get a UTC date converted to a localized time which gives me the day before the one actually in the db.

I have a similar problem, my dateonly in mssql returns a date always with one day less, I even created a question in the stack but still no answer.

Sequelize: 5.7.6
Tedious: 6.1.1
Sql Server 2014 express

tonyguinta · 2019-05-21T15:26:03Z

Same problem in MSSQL using DATEONLY.

As an example, when querying the database directly the date is '2019-06-01', but Sequelize returns '2019-05-31'. I'm in the US Central timezone, so I assume it is still treating it as a UTC date and converting it to my timezone.

It is only returning the string in 'YYYY-MM-DD' format, which is good, but it is converting it based on my local time, which is bad.

Sequelize: 5.8.5
Tedious: 6.1.1
MS SQL Server 2017

jpierrep · 2019-10-02T16:43:29Z

Same Problem, DATEONLY type returns DATE complete from mysql
Sequelize 5.5.0

silveoj · 2020-01-24T18:08:51Z

Has anyone found workaround?
We updated sequelize from 4 to 5.
We use useUtc: false and timezone options.
Writing in DB works correctly.
But reading doesn't work:

for DateTime we get JS Date as in Sequelize 4. It's good.
for DateOnly (without time in table definitions) we get string, not Date.

I tried dateStrings: true/false, typeCast: true/false/Function. No results, no information about it in docs. Were they removed?

mssql: 6
tedious: 6 - 8
sequelize: 5.21.3

I suggest this workaround:

// You can use DATEONLY type instead of DATE. With DATEONLY  you don't need to use typeof
Sequelize.DATE.prototype._sanitize = function _sanitize(value) {
  if (typeof value === 'string') { // maybe check if string date is valid
    return moment(value).utc(false).toDate(); // I use useUTC: false
  }
  return value;
};

- Move signInEmail query to mutation with lacking useLazyQuery - Currently, useLazyQuery does not return promises apollographql/react-apollo#3499. - Change `status` to `statusMessage` and do migration - Fix some of migration scripts. * Add and update types for User - Add `isOnline`, `lastSignedIn` fields. - Changed to DATEONLY type for birthday and prevent being parsed to javascript date (sequelize/sequelize#4858).'

jeancabral · 2020-03-13T23:52:46Z

Sequelize should return only date part for DATEONLY fields. I am using below workaround in my model definitions for all DATEONLY fields and it seems to be working fine so far:
    regDate: {
      type: DataTypes.DATEONLY,
      get: function() {
        return moment.utc(this.getDataValue('regDate')).format('YYYY-MM-DD');
      }
    }

this work for me

peabnuts123 · 2020-04-16T22:02:10Z

This isn't true any more, right? this.getDataValue('myDateOnly') seems to return a YYYY-MM-DD string now. I had to actually write a getter/setter to convert it to a Date object …

Vaelek · 2020-10-07T20:08:08Z

How is this still open after 5 years?

imhappyfor · 2020-10-12T19:05:09Z

@Vaelek because it's still a problem, at least it is for me when using Sequelize 5, MSSQL with date column (remote db, have no control to change it!). Also supplying {raw:true}/{timezone: "-5:00") doesn't work either.

pgcath · 2021-02-25T16:56:34Z

Can confirm that this is a problem. We're having the same issue with Sequelize 5 and MSSQL. We can use elements of the workarounds above, but this is far less than optimal in production.

Any updates?

igolskyi · 2021-12-07T22:53:57Z

My answer on StackOverflow for Postgres, but I duplicate it here.
For correct using queries with timezone, prepare your pg driver, see details here:

const pg = require('pg')

const { types } = pg

// we must store dates in UTC
pg.defaults.parseInputDatesAsUTC = true

// fix node-pg default transformation for date types
// https://github.com/brianc/node-pg-types
// https://github.com/brianc/node-pg-types/blob/master/lib/builtins.js
types.setTypeParser(types.builtins.DATE, str => str)
types.setTypeParser(types.builtins.TIMESTAMP, str => str)
types.setTypeParser(types.builtins.TIMESTAMPTZ, str => str)

It's must be initialized before initiating your Sequelize instance.

You can now run a query indicating the timezone for which you want to get the date.

Suppose we make a SQL query, select all User's fields, and "createdAt" in timezone 'Europe/Kiev':

SELECT "createdAt"::timestamptz AT TIME ZONE 'Europe/Kiev' FROM users WHERE id = 1;

# or with variables
SELECT "createdAt"::timestamptz AT TIME ZONE :timezone FROM users WHERE id = :id;

For Sequelize (for User model) it will be something like this:

sequelize.findOne({
  where: { id: 1 },
  attributes: {
    include: [
      [sequelize.literal(`"User"."createdAt"::timestamptz AT TIME ZONE 'Europe/Kiev'`), 'createdAt'],
      
      // also you can use variables, of course remember about SQL injection:
      // [sequelize.literal(`"User"."updatedAt"::timestamptz AT TIME ZONE ${timeZoneVariable}`), 'updatedAt'],
    ]
  }
});

KrisLau · 2022-02-15T19:26:19Z

My answer on StackOverflow for Postgres, but I duplicate it here. For correct using queries with timezone, prepare your pg driver, see details here:

const pg = require('pg')

const { types } = pg

// we must store dates in UTC
pg.defaults.parseInputDatesAsUTC = true

// fix node-pg default transformation for date types
// https://github.com/brianc/node-pg-types
// https://github.com/brianc/node-pg-types/blob/master/lib/builtins.js
types.setTypeParser(types.builtins.DATE, str => str)
types.setTypeParser(types.builtins.TIMESTAMP, str => str)
types.setTypeParser(types.builtins.TIMESTAMPTZ, str => str)

It's must be initialized before initiating your Sequelize instance.

You can now run a query indicating the timezone for which you want to get the date.

Suppose we make a SQL query, select all User's fields, and "createdAt" in timezone 'Europe/Kiev':

SELECT "createdAt"::timestamptz AT TIME ZONE 'Europe/Kiev' FROM users WHERE id = 1;

# or with variables
SELECT "createdAt"::timestamptz AT TIME ZONE :timezone FROM users WHERE id = :id;

For Sequelize (for User model) it will be something like this:

sequelize.findOne({
  where: { id: 1 },
  attributes: {
    include: [
      [sequelize.literal(`"User"."createdAt"::timestamptz AT TIME ZONE 'Europe/Kiev'`), 'createdAt'],
      
      // also you can use variables, of course remember about SQL injection:
      // [sequelize.literal(`"User"."updatedAt"::timestamptz AT TIME ZONE ${timeZoneVariable}`), 'updatedAt'],
    ]
  }
});

Does anyone know of a similar solution for tedious?

ephys · 2022-02-16T09:31:38Z

If you're still experiencing this with Sequelize 5, you should update to Sequelize 6. Sequelize 5 is not maintained beyond security fixes anymore.
I can't guarantee this is fixed in Sequelize 6 but I have not personally encountered it since

armordog · 2022-02-24T19:24:19Z

I'm using Sequelize 6 with the 'postgres' dialect (Redshift).
And it still returns a JS Date (with the timezone screwed up) no matter what I do.
I've tried

setting the column type to anything else (eg: STRING)
pg.types.setTypeParser...
an afterConnect hook that replaces the DATE type with a CustomData class that overrides parse

Can anyone tell me how to get Dates from a Redshift database in an actually usable fashion?

ephys · 2022-02-25T10:39:43Z

@armordog If you're using DATE instead of DATEONLY, then it's unrelated to this issue.
It's weird that using DataTypes.STRING returns a Date. If you open another issue with a SSCCE that replicates the bug in question, we can take a closer look at it

armordog · 2022-02-25T17:08:13Z

Update: I see it has nothing to do with Sequelize as it's node-postgres that's parsing everything as Date().
@igolskyi's solution worked for me but I didn't realize I had to set the type parser for the database column type (ie: TIMESTAMP), not the type I was specifying in the Sequelize model.

It's still weird that the column type assigned in Sequelize doesn't do anything.

Thanks, @ephys .
I've tried using
date: { type: DataTypes.DATE }
date: { type: DataTypes.DATEONLY }
date: { type: DataTypes.STRING }
date: { type: DataTypes.BIGINT }
date: { type: 'Please don\'t parse this as a date' }

All of them return a parsed JS Date.

I'll make an SSCCE 👍

ephys · 2022-02-25T19:18:44Z

If your column type is actually TIMESTAMP then no need to open a SSCCE as it's not a sequelize bug (it only would have been a bug if both your DataType & Column Type were strings).
I do plan on making the behavior you're fighting against configurable, in the future: https://gist.github.com/ephys/50a6a05be7322c64645be716fea6186a#support-for-alternative-js-types

ephys · 2022-02-25T19:20:17Z

I'll close this issue as I haven't had feedback that Sequelize 6 still returns DATEONLY as Date instances

theoephraim · 2022-03-21T19:29:20Z

Plenty of reasons to want your DATEONLY fields as a string, but just wanted to chime in with a little tip for anyone getting very confusing incorrect dates due to the date conversion. At least this way the extra time info is just a nuisance and shouldn't cause any issues.

One of the first things I always do when setting up a new project is make sure everything is running in UTC. Most deployed cloud/serveless/etc servers are likely to be set up this way by default already, so you're usually just making sure your local env and any edge cases are matching. To do this

1. Set your database timezone to UTC

Run this SQL (for postgres) - ALTER DATABASE postgres SET timezone TO 'UTC'"); SELECT pg_reload_conf();
Run it once, add to your migrations, etc... Just make sure your db is operating in UTC.

2. Set your running node process to operate in UTC

process.env.TZ = 'UTC';
Just add this to a setup file that runs before just about anything else

janmeier added the type: bug label Nov 13, 2015

janmeier mentioned this issue Dec 9, 2015

DATEONLY not return #5005

Closed

janmeier mentioned this issue Aug 10, 2016

How to configure column without timezone in timestamp? #2572

Open

felixfbecker added this to the 4.0 milestone Aug 26, 2016

felixfbecker mentioned this issue Aug 26, 2016

Do not return DATE fields as Javascript Date brianc/node-pg-types#50

Closed

sushantdhiman mentioned this issue Oct 16, 2016

(fix) DATEONLY parsed as Date #6709

Merged

4 tasks

sushantdhiman self-assigned this Oct 16, 2016

sushantdhiman added the pr:final review label Oct 16, 2016

janmeier closed this as completed in #6709 Nov 1, 2016

sushantdhiman removed the pr:final review label Dec 3, 2016

ychi mentioned this issue Feb 16, 2017

和行程相關的日期不該需要擔心時區 wesley100002001/hsin-travel#16

Closed

This was referenced Apr 3, 2017

Grp1 model fixes MusicConnectionMachine/api#72

Closed

Handling Sequelize's incapability to Handle DATEONLY MusicConnectionMachine/api#76

Merged

RichAyotte mentioned this issue Jul 17, 2018

ISO date format support mickhansen/graphql-sequelize#621

Merged

papb reopened this Oct 30, 2019

papb assigned papb and unassigned sushantdhiman Oct 30, 2019

papb removed this from the 4.0 milestone Oct 30, 2019

papb added status: awaiting investigation topic: dates & times For issues and PRs. Things that involve dates and times. labels Oct 30, 2019

hyochan mentioned this issue Jan 26, 2020

Enhance resolvers with integration hyochan/hackatalk-server#25

Merged

1 task

ephys closed this as completed Feb 25, 2022

DATEONLY field is being parsed as a javascript Date with time/zone #4858

DATEONLY field is being parsed as a javascript Date with time/zone #4858

Comments

arieljake commented Nov 13, 2015

dongseok0 commented Feb 2, 2016

denisazevedo commented Apr 13, 2016

lahvey commented Jun 22, 2016

janmeier commented Jun 22, 2016

lahvey commented Jun 22, 2016

janmeier commented Jun 22, 2016

reorg3 commented Jul 25, 2016

felixfbecker commented Jul 26, 2016

Poitrin commented Aug 12, 2016

palodequeso commented Aug 26, 2016 • edited Loading

felixfbecker commented Aug 26, 2016 • edited Loading

afucher commented Jan 12, 2017

sushantdhiman commented Jan 12, 2017

shanmohod commented Mar 6, 2017

shanmohod commented Mar 6, 2017

cupliz commented Jun 5, 2017

felixfbecker commented Jun 9, 2017

javiertury commented Aug 29, 2018 • edited Loading

Vaelek commented Apr 22, 2019

andersonmendesdev commented May 5, 2019 • edited Loading

tonyguinta commented May 21, 2019

jpierrep commented Oct 2, 2019 • edited Loading

silveoj commented Jan 24, 2020 • edited Loading

jeancabral commented Mar 13, 2020

peabnuts123 commented Apr 16, 2020

Vaelek commented Oct 7, 2020

imhappyfor commented Oct 12, 2020

pgcath commented Feb 25, 2021

igolskyi commented Dec 7, 2021

KrisLau commented Feb 15, 2022

ephys commented Feb 16, 2022

armordog commented Feb 24, 2022

ephys commented Feb 25, 2022 • edited Loading

armordog commented Feb 25, 2022 • edited Loading

ephys commented Feb 25, 2022 • edited Loading

ephys commented Feb 25, 2022

theoephraim commented Mar 21, 2022 • edited Loading

1. Set your database timezone to UTC

2. Set your running node process to operate in UTC

palodequeso commented Aug 26, 2016 •

edited

Loading

felixfbecker commented Aug 26, 2016 •

edited

Loading

javiertury commented Aug 29, 2018 •

edited

Loading

andersonmendesdev commented May 5, 2019 •

edited

Loading

jpierrep commented Oct 2, 2019 •

edited

Loading

silveoj commented Jan 24, 2020 •

edited

Loading

ephys commented Feb 25, 2022 •

edited

Loading

armordog commented Feb 25, 2022 •

edited

Loading

ephys commented Feb 25, 2022 •

edited

Loading

theoephraim commented Mar 21, 2022 •

edited

Loading