Compare postgres Timestamp with JavaScript Date Object

Question

I am using Node.js with pg to query a Postgres database. I am trying to execute the following query:

SELECT COUNT(*) FROM table_name WHERE date_time_added::date = some_date;

some_date is the date I pass from Node.js.

I have no problems with this most of the time, as both date/time strings are in the same format. However, issues arise when Timezones are introduced. As I am located in the UK, we use British Summer Time (GMT+1) for some parts of the year.

When I cast date_time_added from it's stored type (timestamp with timezone) to date, the time defaults to midnight. The issue with this is that during BST, the timestamp is cast to one day earlier, and the time set to 23:00 instead of 00:00, as shown below:

{ date_time_added: 2020-09-28T23:00:00.000Z }

This wouldn't be a problem if JavaScript had the same behaviour, however if I try the following:

const date1 = new Date(2020, 8, 29, 10, 47, 54);
console.log(date1.toString());
date1.setHours(0);
date1.setMinutes(0);
date1.setSeconds(0);
date1.setMilliseconds(0);
console.log(date1.toString());

The following output is produced:

"Tue Sep 29 2020 10:47:54 GMT+0100 (British Summer Time)"
"Tue Sep 29 2020 00:00:00 GMT+0100 (British Summer Time)"

The time is set to midnight rather than 23:00 the day before. This is a problem for me as this means the dates don't match, and therefore the row is not returned in the database query.

Does anyone know how I can get the same behaviour for both dates (JS and Postgres)? It would be good to be able to initialise a new Date Object with the time defaulting in the same way as Postgres does when I cast to date, however I am not sure if this is possible.

I think that you are misusing the Date object. You are explicitly setting the time part (hours, minutes, seconds) to 0 - why do you expect then to get a non-zero result ? If all your timestamps are produced by Node - then you should compare them in JavaScript. But if some timestamps are coming from the now() function in Postgres - then it is better to compare the timestamps inside your SQL queries. — IVO GELOV
– IVO GELOV, Commented Jan 20, 2021 at 16:35

user14131782 · Accepted Answer · 2021-01-21 14:38:28Z

3

Posting in case anyone else comes across this problem in the future.

This issue was caused because casting a timestamp to a date in Postgres loses the timezone, therefore it was subtracting an hour from midnight (as the time is set to midnight when casting a timezone to a date), causing it to become 23:00 one day before the date (as BST is +1 hour).

I decided to use epoch time as this made comparison easier for my needs.

I solved this by using the following query:

SELECT COUNT(*) FROM table_name WHERE extract(epoch from date_trunc('day', date_time_added)) = some_date_epoch;

This keeps the timezone and returns the epoch time (at midnight) for the date of the selected timestamp. 'some_date_epoch' is the epoch time of a JavaScript Date Object, obtained using the Date.getTime() method. This is the epoch time of the date I'd like to search for in the database.

For example, this is how I'd get the epoch time for the current date in JavaScript, to then use in the database query:

let currentDate = new Date();
currentDate.setHours(0);
currentDate.setMinutes(0);
currentDate.setSeconds(0);
currentDate.setMilliseconds(0);
let currentDateEpoch = (currentDate.getTime() / 1000); //Convert to seconds to match Postgres

currentDateEpoch would then be used to query the database.

answered Jan 21, 2021 at 14:38

user14131782

1,0783 gold badges16 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

wmp224 Over a year ago

i'm not confident but i believe on larger data sets converting to epoch like this may cause performance issues

user14131782 Over a year ago

As of right now it seems to be performing okay. I'll monitor the situation once I start working with more data and I'll have to change it if the performance is poor. Thanks for the heads up

Collectives™ on Stack Overflow

Compare postgres Timestamp with JavaScript Date Object

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related