Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Dates/times locked to absolute time values rather than floating (and: DATETIME_FORMAT caching)?

Topic Labels: Dates & Timezones
1829 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Nick_Rothwell
5 - Automation Enthusiast
5 - Automation Enthusiast

I created a bunch of entries with start and finish times such as 10:00 / 14:00 a couple of months ago. Now that we’re out of BST and into GMT they’re all noted as 11:00 / 15:00 - i.e. they appear to be absolute time values displayed in the browser’s local time zone. Is this intentional? If so, is there a way to work in “floating” wallclock times?

These are fields of type Date - yet if I pretty-print them in another column using DATETIME_FORMAT(_From, ‘HH:mm’) they revert back to the original values, so a Date currently appearing as 11:00 formats as 10:00. This is the value I want to see, but I don’t understand the logic which produces it.

2 Replies 2
Nick_Rothwell
5 - Automation Enthusiast
5 - Automation Enthusiast

On the latter: it appears that the DATETIME_FORMAT output strings were generated when we were in BST and then cached, and so didn’t change when we shifted out of BST. Modify the Date values (to their current values!) and the formatted values change. This can’t be right, surely?

Alexander_Soro1
6 - Interface Innovator
6 - Interface Innovator

Hi Nick,

It’s expected. You’re using DATETIME_FORMAT(_From, ‘HH:mm’) without setting a timezone. It then uses UTC, which have accidentally worked for you before. You should do something like DATETIME_FORMAT(SET_TIMEZONE(_From, ‘Your timezone’), ‘HH:mm’) .

Alex