0

I can't figure out how to handle the timezone efficiently with dynamodb get operation.

Say my partition primary key and range key (sk) are below, where each record is aggregated data for the whole day done by the user. 2022-09-13 00:00:00 to 2022-09-13 23:23:59

{
 pk: 'userId',
 sk: '2022-09-13'
}

What is the best approach to store the dates as UTC and fetch based on the client's timezone?

In the current behavior of my program, I'm saving the range key (sk) as UTC date. But if the client's local timezone is +8:00, and when he performs a save operation at 2022-09-14 00:01:00, it will still be stored in the 2022-09-13.

Ruelos Joel
  • 2,209
  • 3
  • 19
  • 33
  • Your question isn't necessarily specific to Dynamo, and it's been asked many times before. You've first got to work out the business logic for the behavior you want. The general problem is: When summarizing transactions by date, *whose date is it?* It could be yours, or the clients, or that at a particular location or time zone, or UTC. Any of those are valid - it's up to you to work out what the business requirement is. – Matt Johnson-Pint Sep 13 '22 at 20:25
  • @MattJohnson-Pint but as a best practice, it's said to store dates in UTC, but my problem would be the client time difference. – Ruelos Joel Sep 14 '22 at 01:59
  • That "best practice" advice is short sighted. There are many cases where UTC is not appropriate. "Business days" and whole dates in general are among them. There are too many others to list here, but search and you'll see this has been brought up many times before. "Always UTC" is wrong. UTC is valid for many cases, but not all of them. – Matt Johnson-Pint Sep 15 '22 at 20:35

1 Answers1

0

Just store a timestamp (date + time) in UTC instead of the date.

If the client needs data based on the local timezone, you calculate the timestamp of the day start and end in that timezone, shift it to UTC and then do a between query using the utc-start and utc-end.

Maurice
  • 11,482
  • 2
  • 25
  • 45
  • 1
    What is the time to be stored in the UTC? Is it 00:00:00 because this is the whole record for a single day. Like a daily summary – Ruelos Joel Sep 13 '22 at 17:23
  • Well, if you want your day definition region-specific, you can't have _one_ day definition in your aggregate. You'd have to aggregate hourly (or every 30 minutes if you want to account for timezones like +05:30). That's a business decision though. – Maurice Sep 14 '22 at 04:35
  • Thanks for the input, i found this question similar to my use case. https://stackoverflow.com/questions/44965545/best-practices-with-saving-datetime-timezone-info-in-database-when-data-is-dep, basically my date column would be client date, but the update time would be in UTC – Ruelos Joel Sep 14 '22 at 04:51