UTC to local date/time?

UI Builder
1

I believe the Backendless DateTime column type is stored in UTC. For example, if a row is inserted into the DB at 8 am today (1/30/2023), it would get inserted as 13:00, as Eastern Standard Time is UTC -5.

However, If a row is inserted into the DB at 8 am on 3/13/2023, it would get inserted as 12:00 as Eastern Daylight Time is UTC -4.

From the user’s perspective, these two inserts happened at the same time, but the DB has two different times. How are folks working around this?

I was thinking about building a function that would take a date and timezone and return the UTC +/-. What is everyone else doing?

Thanks,
Tim

2

Hollo @Tim_Jones

DateTime is stored in the timestamp which is not related with time zone, but a client Date class transforms the timestamp to a human-readable string using the current time zone of the system

When you save/get objects to/from the server you can see such properties “created/update” contains timestamp number and for searching, I would recommend you use timestamps as well.

Regards,
Vlad

3

Hi @vladimir-upirov,

Thanks for jumping in. What codeless block is the client Date class you mentioned? I don’t see a block where I can input a date and a date format to get the results I want.

Tim

4

UI Builder - aa11aa - Backendless 2023-01-30 18-17-38
UI Builder - aa11aa - Backendless 2023-01-30 18-17-381920×1199 107 KB

5

That was very helpful; thank you. I didn’t realize the date from timestamp block converted to the local date/time.

What happens if the date from timestamp block is run at Cloud Code by a timer? What is considered local time?

6

it will be the server’s OS time

7

That makes sense.

How do I query the DB to account for timezone offsets? For example, I want to get all the rows created on an eastern time day. Depending on the time of year, that is -4 or -5 offset from UTC.

I appreciate the help. Date/time gives me a headache.

Tim

8

Since the database stores the data in the UTC0 timezone, you’d need to run the query with that in mind. For instance, if you specified a date/time range on your query, the range values would need to be in UTC0.

Regards,
Mark

9

Would that have to be in two different queries and then merge the results?

For example, how many rows were inserted between 8am and 9am each day of the year would have to be two queries to account for standard time and daylight time, right?

This is why date/times give me a headache.

10

See the datetime functions documented here:
https://backendless.com/docs/rest/data_database_functions.html#datetime-functions

The where clause query would look like this:

HOUR( dateTimeColumn ) = 8

This will capture all the timestamps where the time is between 8:00 and 8:59

11

I should have clarified. If I wanted to see how many rows were created between 8-9am local time. I assume HOUR is just a conversion of the timestamp in UTC0 to the hour, still in UTC0.

12

convert 8-9am local time to UTC0 and use that number in the query.
To get the count, use the count function with the where clause.

13

Hey @mark-piller,

I appreciate you jumping in here. I’m not trying to be obtuse, but this is a nuanced discussion.

I am trying to confirm that 8-9am local time to UTC0 is different depending on the time of the year because of daylight savings time. It is -5 during standard time, and -4 during daylight time.

It gets more important, and complicated when thinking about how many inserts happened in a day, and as those days transition from standard to daylight time.

Tim

14

I totally understand, it can get tricky, especially with daylight savings time. Your algorithm needs to account for the period when daylight saving is in effect and to add to the punch, it needs to be aware of the geographic differences, for instance, Arizona does not observe Daylight Saving Time, with the exception of the Navajo Nation…

15

This is why working on datetime data gives me a headache.

Does anyone have any examples of how they’re dealing with this to give users an accurate view of the data in their timeframe?