Best way to get all rows created today, in local time?

[Tim_Jones]

As I understand it, created, the system column, is in UTC. Because of daylight savings time, the offset between UTC and East Coast Time is -4 or -5. So I can’t simply use a fixed offset.

I haven’t found a code block (this is what I’m probably missing) that can tell me the current offset to UTC.

So this is my solution. Does anyone have a suggestion for a better way?

Thanks,
Tim

[mark-piller]

Here’s a simpler version:

[Tim_Jones]

@mark-piller

Your example doesn’t seem to adjust for my timezone, or am I missing something? This logic -

Returns 1691625600000 for today 8/10

https://www.epochconverter.com/ says that timestamp is -

GMT : Thursday, August 10, 2023 12:00:00 AM
Your time zone : Wednesday, August 9, 2023 8:00:00 PM [GMT-04:00]

Using that in a query would return rows created from 8:00:01 to 12:00:00 yesterday in local time. The goal is to only return rows created today in local time.

Thanks,
Tim

[mark-piller]

Timestamps are stored in the UTC time on the server. The logic I shared does the following:

1. Gets the current local time.
2. Sets the hours, minutes, seconds, and milliseconds to 0’s in the local time
3. Converts the local time to UTC and uses that timestamp in the query.

As a result, the query will return all records created today after 00:00:00 in the local timezone.

[mark-piller]

Correction - the logic sets UTC hours, minutes, seconds… So you’re right, instead it should set local hours, minutes, and seconds rather than UTC:

[Tim_Jones]

@mark-piller I didn’t realize the Date Now block returned local time.

Is it possible there is a bug in the set " " for date block? If I set the hours to zero for Date Now, I would expect that to retain the local time. However, the result for set hours and set UTChours are identical -

Thu Aug 10 2023 00:11:47 GMT+0000 (Coordinated Universal Time)
Thu Aug 10 2023 00:11:47 GMT+0000 (Coordinated Universal Time)

I would expect dateNowLocal to retain the time offset of -4 for Boston OR be offset of GMT by -4. This functionally turned local time into GMT.

Tim

[mark-piller]

This is what I see with the following logic:

Result:

Try using the print block to see what the values actually are.

[Tim_Jones]

It seems the behavior works differently between UI Builder and Codeless -

UI Builder - Functions more or less as I would expect -

Local time 00:00:00 - Thu Aug 10 2023 00:00:00 GMT-0400 (Eastern Daylight Time)
UTC time 00:00:00 - Wed Aug 09 2023 20:00:00 GMT-0400 (Eastern Daylight Time)

Codeless - Doesn’t work how I would expect -

"Local time 00:00:00 - Thu Aug 10 2023 00:00:00 GMT+0000 (Coordinated Universal Time)
UTC time 00:00:00 - Thu Aug 10 2023 00:00:00 GMT+0000 (Coordinated Universal Time)"

[Tim_Jones]

@mark-piller are you testing this in UI Builder? I am guessing you are, based on your suggestion of using prints. I am working in Codeless and it returns different results. See above.

Tim

[mark-piller]

I assume by “Codeless” you mean Cloud Code? Codeless is the visual programming system that is present in both UI Builder and Cloud Code.

In the case of Cloud Code, the behavior IS going to be different, because in UI Builder the browser itself does the timestamp conversions. In Cloud Code the returned date is always in UTC, there is no concept of the local timezone

[mark-piller]

Btw. print also works in Cloud Code. In that case, anything you “print” will go to the server log (and can be seen in the real-time logging window)

[Tim_Jones]

You are correct. I was calling Cloud Code, codeless.

My original code is Cloud Code. I’m working on a timer that sends reports, so I have to localize the time.

Based on that understanding, is there a better way to get Eastern time other than the “Convert to locale date” block?

Tim

[sergey.kuk (Sergii Kukurudziak)]

hello @Tim_Jones

the best way is to use “Convert to locale date” block. Like the following:

[Tim_Jones]

Thanks @sergey.kuk! This is perfect and much appreciated.

Tim