Date and Timezones

[leore]

We are saving '2019-05-12' to the DB as a datetime.

My computer is in PST.

Before I save via Bookshelf the data is:
Sat May 11 2019 17:00:00 GMT-0700 (PDT)

After I save to Bookshelf I get:
Sat May 11 2019 00:00:00 GMT-0700 (PDT)

When I format this date with '/(\d{4})-(\d{2})-(\d{2})/'

I get '2019-05-11'

Therefore all my tests fail because '2019-05-11' does not equal '2019-05-12'

Bookshelf is assuming i want UTC (which I do in production) but when I test locally i want to save as my system time.

My production machine is in UTC. Therefore I think the way to solve this is that Bookshelf should store and save using machine time.

If there is around this please let me know but any tests i create with dates all fail and this is a pretty serious issue for us.

[tgriesser]

Yeah, I think dates might need to be documented a bit better between knex / bookshelf - they can just be a bit tricky when trying to standardize between different databases when needing to account for the time on the server vs the database (shouldn't be an issue with datetime though), different environments, and the client vs the server.

So first, I'm a bit confused - because those values aren't equal...

new Date('Sat May 11 2019 00:00:00 GMT-0700 (PDT)').valueOf() !== 
   new Date('Sat May 11 2019 17:00:00 GMT-0700 (PDT)').valueOf()

When you say "After I save to Bookshelf I get:" do you mean after you save and then fetch from the database?

At what stage are these dates being parsed, and which database are you using.

[leore]

yes the fact that they are not equal is throwing me off too.

The first is the value that I am saving.

The second value is the one I receive when I call fetch.

I am using PG.

I parse the value after I fetch from the DB.

[tgriesser]

Is the actual column type a timestamp or timestamptz - note the disucussion here knex/knex#184 and the change in knex 0.6 to use timestamptz by default.

[leore]

the actual column is a date. Should we use another type?

[tgriesser]

I think you'd probably either need to use a different type which keeps track of the timezone or explicitly set the date object you want to save in the format method with new Date(yyyy, mm, dd)

There were a few issues that looked related in node-postgres brianc/node-postgres#510 and brianc/node-postgres#514

As @brianc says at the bottom there "Timezones & string encodings are hard!"

[brianc]

Word!

[leore]

the column was data and i know switched it to timestamptz and it worked

[tgriesser]

Great