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

[Nick_Rothwell]

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.

[Nick_Rothwell]

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]

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