1

I have an issue trying figure out logic for storing time in database in UTC and handling DST changes.

I have TimeStart and TimeEnd in Database which indicates business hours. Problem occurs if I save hours while it's DST and while it's not. If I save 09:00 - 17:00 on October 15th, it would result 07:00 - 15:00 (UTC) but if I would add business hours now when it's not DST it would result 08:00 - 16:00 (UTC). I thought that when converting with moment to UTC would give consistent result taking DST in consideration but I was wrong.

What's best solution to handle that on frontend/backend. Should I substract added DST time? Because using moment to handle conversion to UTC doesn't help as it gives 1 hour difference anyway (now I question everything I did so far with UTC times as problems can probably occur due this issue).

Thanks

greenbigfrog
  • 27
  • 8
yorona
  • 11
  • 2

2 Answers2

3

The advice "always use UTC" is shortsighted. There are many cases to not use UTC, and the one you described is certainly among them. The fact is, UTC is not good for future scheduling - especially with recurrence patterns.

Simply store the local start and end time of the business hours, without a date. You might also want to store the time zone identifier (such as "America/Los_Angeles") of that business's location.

When determining the business hours for a specific date, then apply the time from your database to the date in the business's time zone. To know if the business is open or not, convert the result to UTC to test against the current UTC time.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
0

Since I don't have the necessary reputation to simply leave a comment, I guess I'll write an actual answer. (Disclaimer: I haven't actually worked with moment.js)

I would recommend you always store time as UTC in your database. Conversion should happen when displaying/storing in frontend.

For more info on how to handle DST I found this great question with a lot of answers. You should be able to apply a lot of the stuff mentioned there to any stack: Daylight saving time and time zone best practices

Given the details in the question, it's hard to tell you where your error is, that you're still getting wrong values.

greenbigfrog
  • 27
  • 8
  • Thanks, I always have stored times as UTC in my database but I feel like there was always this issue with DST. My exact issue is if user comes and stores business hours in UTC when there's DST, that will result saving 07:00 (utc) in Database (as I'm in +2 zone). But if user adds business hours now then there's no DST it will result as 08:00 in database (as utc). There's that inconsistency... I'm using moment(date).utc() to convert from local to UTC. – yorona Oct 30 '19 at 15:04
  • Oh. I see. I guess storing the location of the business (timezone) is relevant here. – greenbigfrog Oct 30 '19 at 16:42