Postgres + Javascript + Timezone

Question

My postgres database has users table and I am storing creation date in UTC (DB timezone is not set and is UTC). So if someone from NY signed up on 26 March at 11PM Local time, it will actually be saved as 27 March 3AM, so far so good. The issue I have is when I display that as stats, as in how many users were signed up today or in a specific date, things get hairy. Do I need to send the DB from the user's timezone and query the DB based on that?

yep, "...signed up today..." requires you to decide about a time zone reference. Ask the user, he/she needs to decide what's the "reference time zone". And get that in written, just sayin' — The Impaler
– The Impaler, Commented Mar 28, 2020 at 12:02
Agreed. The concept of "today" depends on a time zone. In some cases you will want the user's time zone. In other cases it may be your business's time zone, UTC, or some other time zone. Once you decide, you can convert from start/end of day in that time zone to UTC, then use those values to query your db. — Matt Johnson-Pint
– Matt Johnson-Pint, Commented Mar 28, 2020 at 20:03

Gordon Linoff · Accepted Answer · 2020-03-28 12:09:42Z

1

You can convert to the local timezone with:

SELECT timezone('America/New_York', created_at)::date

Here is a db<>fiddle, which at most times will give different dates.

edited Mar 28, 2020 at 12:09

answered Mar 28, 2020 at 12:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Postgres + Javascript + Timezone

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related