Sequelize, Postgres. Save date including UTC offset

Question

I have an app that creates some entities (it doesn't really matter for this question), but the entities could be created in different timezones, so I have to store UTC offset in my createdAt and updatedAt columns. For some reasons, I'm not able to save generated date correctly, please refer to the code snippet below, to get more details:

Here is what I'm doing:

  const { user } = userInfoWebAPICallResult as WebAPICallResult
  const now = moment(moment.now())
    .tz(user.tz!)
    .toDate();

  const entity = await Entity.create({
    ...someData,
    createdAt: now
    updatedAt: now,
  });

I'm getting tz value externally, it's a string which looks like "America/Los_Angeles". If I convert now to string (by calling .format() method) and log the received value I will see something like this "2020-01-03T16:32:53-08:00". As you can see the date has offset value, this is the correct date, but during inspecting the Database I see "2020-01-04 00:32:53.013 00", this string doesn't have offset value anymore and it looks like the resulting date was computed automatically.

I will need to implement the filtration by createdAt date, people from different timezone will use this filtering mechanism. I planned to expose two parameters date and utcOffset via API, which would allow me to return correct entries depending on the user time zone.

What I'm missing?

Postgres does NOT store the offset. Further storing the offset would be wrong about 1/2 time, and neither should you. It would not account for daylight saving. What you need to do is store the tz and then select date_tims AT TIME ZONE tz. Postgres will automatically adjust for daylight savings. BTW: How did you inspect the database. — Belayer
– Belayer, Commented Jan 4, 2020 at 1:40
@Belayer thanks for the answer. Are there any other ways to solve my issue without storing tz in the database? What if I store dates as a number, by converting them in the following way: ` const now = moment .utc(moment.now()) .utcOffset(user.tz_offset) .unix(); ` That should give a number which already aligned with timezone Regarding DB inspection. I just selected all rows from the table using TablePlus — TK-95
– TK-95, Commented Jan 4, 2020 at 13:55
You probably can do that, I wouldn't even consider it. I live by very simple database rules. One of them being dates, time stamps, etc are always stored as the appropriate date type. Anything else I'll assure you will later regret. Before you embark on such a complex system consider: Maintenance: What do do you do when when the currently unconsidered requirement is to generate a report for the last 6months broken down by week. And, how would you like to inherit such a design as you propose when the original developer is gone and there is insufficient or no documentation. — Belayer
– Belayer, Commented Jan 4, 2020 at 19:37
That entire expression where you create the now variable is exactly the same as just const now = new Date(); — Matt Johnson-Pint
– Matt Johnson-Pint, Commented Jan 4, 2020 at 21:23

Eric · Accepted Answer · 2020-01-04 20:43:47Z

1

The moment when the entity was created / updated should probably be stored in UTC; it is a timestamp that identifies an exact point in time when the event happened.

I would suggest dealing with time zones on the output part: convert a UTC timestamp to a display that takes the user's time zone into account.

If you need to know what time of day it was for the user who created or updated the entity, I would store that user's time zone in a separate column, using standard time zone identifiers (like "America/New_York" rather than "EDT" or "EST").

answered Jan 4, 2020 at 20:43

Eric

961 silver badge5 bronze badges

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

1 Comment

TK-95 Over a year ago

Seems like this is the best option. I'm not happy to have another column and I would better try to get UTC offset in already existing createdAt and updatedAt columns, but I'm not seeing that it's possible Thanks. Will go with the separate column for the timezone

Collectives™ on Stack Overflow

Sequelize, Postgres. Save date including UTC offset

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related