0

I am confused about using datetimes in databases and applications for international users. My application is developed asp.net web api and c# programming language.

I am using a column in database (postgresql) named created_at that type is without timezone.

When New York Time 2020-11-02 23:50:00, London Time is 2020-11-03 04:50:00. So day and hour is different.

And I want to show dates in relative time like Now, 1 Hours ago, 10 minutes ago...

If Someone adds a record in database at 2020-11-03 04:50:00 in London, someone shows the record as relative time 5 hours after. This looks a little strange.

Is this a true logic? How does it workin real applications?

barteloma
  • 6,403
  • 14
  • 79
  • 173
  • 1
    `DateTime.UtcNow` https://learn.microsoft.com/en-us/dotnet/api/system.datetime.utcnow – fubo Nov 03 '20 at 07:07
  • 1
    _"How does it workin real applications?"_ You'd normalize to _one_ specific timezone internally. Most dev teams would probably chose UTC, since it is designed exactly for this. Convertion to local times only come into play at the last step: the UI. – Fildor Nov 03 '20 at 07:15
  • ^^ Be aware that there are still a lot of pitfalls, but most are well documented and there are plenty of "best practices" articels to be found via the search engine of your choice. – Fildor Nov 03 '20 at 07:24
  • What type will be database column? – barteloma Nov 03 '20 at 07:43

1 Answers1

0

You could store your datetime as UTC and convert it to the users time zone correspondingly. Or you could store your values in postgreSQL via its designated time types.
(see https://www.postgresql.org/docs/9.1/datatype-datetime.html)

lukger
  • 404
  • 3
  • 11
  • So briefly, how will he see in New York as relative if I use UTC? – barteloma Nov 03 '20 at 07:25
  • 1
    @barteloma You need send the UTC timestamp to your frontend/UI (browser if a recall). There determine the timezone of the user (e.g. with [this](https://stackoverflow.com/questions/1091372/getting-the-clients-timezone-offset-in-javascript)) and calculate the local time in UI from it. Then substract that value from the "localtime now" value also gathered in the UI. – Christian.K Nov 03 '20 at 07:56