Changing the time offset to GMT -05:00

[Quito]

Hi,

I have this code that is giving me the Host (server) time. It seems that the GMT offset can be included to show the Local time but I'm stumped. Can someone tweak this for me?

Many thanks beforehand,

Daniel

______________________

Let (     [ts = GetAsTimestamp ( Get(CurrentHostTimestamp))  - $$LOG_DATE_OFFSET;     secondsIncluded = If ( PatternCount (  GetAsTimestamp ( Get(CurrentHostTimestamp))  - $$LOG_DATE_OFFSET; ":") = 1; 1;0);     start = Position (ts; ":" ; 1 ; 1 ) + 2;     length = Length ( ts );     insert = ":00";     end = Right ( ts ; 3 )]; If ( secondsIncluded;     Left ( ts ; start ) & insert & end ;     ts ) )

[comment]

I am having a hard time understanding what your question is. Perhaps you could clarify by answering:

• Is your file served on FileMaker Cloud?
• What do you get as the result of Get(CurrentHostTimestamp)?
• Do you know the host's local timezone offset?
• What exactly are you trying to calculate: the host's local time or ... ?

 

 

[Quito]

Hi, Comment,

I'll try to explain further:

• Is your file served on FileMaker Cloud?

Yes, it's on FileMaker Cloud

• What do you get as the result of Get(CurrentHostTimestamp)?

This very instant, Get(CurrentHostTimestamp) retrieves 4/7/2023 16:32:35 and my current local time is 11:32 (GMT -05:00).

• Do you know the host's local timezone offset?

No, I don't. FileMaker Cloud provides the UTC time. I've changed the Cloud settings to my local time and nearest location but it disregards that personalized setting.

• What exactly are you trying to calculate: the host's local time or ... ?

In the same example, the expected result should have been 4/7/2023 11:32:35

 

Best,

Daniel

[comment]

4 minutes ago, Quito said:

In the same example, the expected result should have been 4/7/2023 11:32:35

IIUC, that is your local timestamp - which you should be able to get directly from Get ( CurrentTimestamp ).

What am I missing here?

 

[Quito]

OK, so I changed Get ( CurrentHostTimestamp ) to Get ( CurrentTimestamp ) and it brings the same time (now it's 4/7/2023 16:52:45), without taking into consideration the local offset.

[comment]

You must be doing something else you're not telling us about. The  Get ( CurrentTimestamp ) function returns the time and date on the client machine. How exactly are you evaluating it?

 

[Quito]

Here are a few tries I've made, that haven't worked:

1. Changed the entire script to: Creation_Timestamp_Host - "07:00:00"

2. In Options/Auto-Enter, selected Creation: Timestamp (Date and Time)

3. Added a minus 7 after LOG_DATE_OFFSET: Let (     [ts = GetAsTimestamp ( Get(CurrentHostTimestamp))  - $$LOG_DATE_OFFSET - 7 ;     secondsIncluded = If ( PatternCount (  GetAsTimestamp ( Get(CurrentHostTimestamp))  - $$LOG_DATE_OFFSET; ":") = 1; 1;0);     start = Position (ts; ":" ; 1 ; 1 ) + 2;     length = Length ( ts );     insert = ":00";     end = Right ( ts ; 3 )]; If ( secondsIncluded;     Left ( ts ; start ) & insert & end ;     ts ) )

3. Added a minus 07:00:00 after LOG_DATE_OFFSET: Let (     [ts = GetAsTimestamp ( Get(CurrentHostTimestamp))  - $$LOG_DATE_OFFSET - 07:00:00 ;     secondsIncluded = If ( PatternCount (  GetAsTimestamp ( Get(CurrentHostTimestamp))  - $$LOG_DATE_OFFSET; ":") = 1; 1;0);     start = Position (ts; ":" ; 1 ; 1 ) + 2;     length = Length ( ts );     insert = ":00";     end = Right ( ts ; 3 )]; If ( secondsIncluded;     Left ( ts ; start ) & insert & end ;     ts ) )

4. Added a quoted minus 07:00:00 after LOG_DATE_OFFSET: Let (     [ts = GetAsTimestamp ( Get(CurrentHostTimestamp))  - $$LOG_DATE_OFFSET - "07:00:00" ;     secondsIncluded = If ( PatternCount (  GetAsTimestamp ( Get(CurrentHostTimestamp))  - $$LOG_DATE_OFFSET; ":") = 1; 1;0);     start = Position (ts; ":" ; 1 ; 1 ) + 2;     length = Length ( ts );     insert = ":00";     end = Right ( ts ; 3 )]; If ( secondsIncluded;     Left ( ts ; start ) & insert & end ;     ts ) )

The strange thing is that sometimes the expected local time gets inserted, but on the next run the tweak may or may not work.

[comment]

30 minutes ago, Quito said:

2. In Options/Auto-Enter, selected Creation: Timestamp (Date and Time)

If that's where you want to have the local date and time to be inserted upon creation of a new record, then that is all you need to do. No additional "tweaks" should be required. Under what circumstances are you getting a different result (and what is that result)?

I am afraid I could not understand the other tries. What script do you refer to in #1? As for the other calculations, they make no sense to me, and I don't understand where you're trying to use them.
 

I suggest you open your data viewer (while the hosted file is open) and enter the following calculation:

List ( 
Get ( CurrentTimestamp ) ;
Get ( CurrentHostTimestamp ) ;
Get ( CurrentHostTimestamp ) - Time ( 5 ; 0 ; 0 )
)

Assuming you are doing this ~10 minutes from now, you should see a result like:

4/7/2023 13:45:31
4/7/2023 18:45:31
4/7/2023 13:45:31

What other result do you need?

 

 

Edited July 4, 2023 by comment

[Quito]

I replaced the entire script (provided at the beginning of this topic) with:

Get ( CurrentHostTimestamp ) - Time ( 5 ; 0 ; 0 )

and it's bringing the current local time successfully.

Thank you very much, Comment!

[comment]

But that was just to show that Get ( CurrentTimestamp ) returns the same result.

You should be aware that the two expressions are not entirely identical. According to www.timeanddate.com, your location has stopped observing Daylight Saving Time in 1993. As long as that is true, subtracting Time ( 5 ; 0 ; 0 ) from Get ( CurrentHostTimestamp ) will always return the local date and time. But in locations that observe DST, the offset is not constant -  and if you want to be sure you're getting the correct local timestamp, you need to use Get ( CurrentTimestamp ).

And if you need this as auto-entered creation timestamp, then you don't need any calculation at all. Just tick the Creation checkbox, select Timestamp (Date and Time) and you're done.

[Quito]

Yes, you are correct in everything, including that we do not observe DST anymore. I'll look into it again soon to see if I can get the same result with your other suggestions and will post results here.

All the very best,

Daniel