Date and Time Are Not Correctly Displayed in the Calculated Result of DATE_FORMAT Function
If the date and/or the time in the calculated result of DATE_FORMAT function is different from what they should look, it is possible that the time zone is not set properly.
The time zone of the field for date and time is fixed at Coordinated Universal Time. To use these fields in formulas, specify the time zone ID "Etc/GMT" of Coordinated Universal Time. When you specify other time zones, you will not obtain the result as you expect.
For example, if you set "Osaka, Sapporo, Tokyo (Asia/Tokyo)" for a time zone, the time shown would be +9 hours.
- Time Zone Calculation - Wrong:
DATE_FORMAT(Field A, "HH:mm", "Asia/Tokyo")
If the value in Field A is "9:00", the calculated result is: 18:00
By specifying UTC Time Zone ID "Etc/GMT", the correct time will be displayed.
- Time Zone Calculation - Correct
DATE_FORMAT(Field A, "HH:mm", "Etc/GMT")
If the value in Field A is "9:00", the calculated result is: 09:00