Log in to AnswerLog in to Follow
UserImage.jpg
Robert Milliken
19Views
5Comments
Make stored datetime consistent across timezones? Best practice?
Question
Reactive
Application Type
Reactive

I want to capture the datetime from the face of a tax receipt and store the face value in the database unmodified.

For example if I have a user in...

  1. London data enter 12 Mar 2022 14:52, I want that entered into the database as 12 Mar 2022 14:52 
  2. New York data enter 12 Mar 2022 14:52, I want that entered into the database as 12 Mar 2022 14:52
  3. Sydney data enter 12 Mar 2022 14:52, I want that entered into the database as 12 Mar 2022 14:52

The current behaviour when using the datetime widget is that the datetime would be stored as UTC. In the 3 examples above, the receipt would have different datetime values in the database.

What is the best practice to resolve this issue? Currently I am leaning towards splitting the Datetime field into two separate fields (date, time) and handling it that way. 

0
0
01 Aug

You can add an attribute to your database with the time zone of that Date Time and then, depending on your application, you can decide the time zone you want to show to the user based on some general setting or location.

0
0
01 Aug

Thanks João, 

I was hoping to avoid that, having three datetimes that are the same value, but are different values in the database just doesn't sit right with me.

I suspect that way, or the split, are the only way to do it for now.

0
0
02 Aug

Replying to Robert Milliken's comment on 02 Aug 2022 01:32:09

Hi Robert,

If for you, they are the same value, then splitting them up in a date and time is an option.

Can I deduce that the OCR software scraping this info from the tax receipt is running in the browser ?  Otherwise (running on server), the face value would be saved.

But think carefully if they really are the same.  Is there never a need to have them in real chronological order / all in same timezone (accounting, reporting,...)  For that type of application, they really are not the same.

In other words, what functionality makes use of this information, other than displaying as accurately as possible what is on the receipt.

I would imagine that you maybe need both at different times in the application, you could store both, or you could store either one of them along with the location/timezone the receipt happened in (as Joao suggested), allowing converting to the other when needed.

Dorine

0
0
02 Aug

Replying to Dorine Boudry's comment on 02 Aug 2022 02:28:35

I was just thinking that your problem is even bigger than this.

If user has a tax receipt for a purchase in New York, but is later back home in London when he's using your application to register it, the value stored would be all wrong.

Come to think of it, the only option is to record/store it as separate date and time (or text, but I like that less), along with location where the receipt originated if you need some sort of unified timeline.

Dorine

0
0
02 Aug

Hi Robert.

If you are storing data from a receipt , maybe you could handle and store them always as text. And only do the convertion of the values at the moment client side or in the logic flow that you need them to handle them

You can save save them as date, but if you have dates like 12 Mar 2022 23:52 depending on your timezone you may have 13 of May instead of 12.

You migh have some alternatives as saving more that one field in DB:

- server date time in UTC

- user browser date time (handled as text)

or 

- add a field like timezone from the location your are getting data of (in this cases avoid daylight saving timezones, since you may have some surprises twice a year) or a calcultion (UTC Offset) that may help you on conversion. I would not recommend a fix offset if the timezone is daylight saving.

There's an old article with a  component that may help you on this:

https://www.nearpartner.com/2018/11/outsystems-multi-timezone-applications/

https://www.outsystems.com/forge/component-overview/500/time-zone




0
0
02 Aug
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.