my query is incorrect. convert_tz is causing incorrect values

Asked Jan 12 '23 at 15:37

Active Jan 12 '23 at 15:39

Viewed 46 times

I use convert_tz for many queries that I use daily. Yesterday, it began yielding incorrect values.

I seperated these lines from my query, and ran them separately.

SELECT 
CURDATE() - interval 1 day as yesterday,
CURDATE() as today, 
CONVERT_TZ(CURDATE(), @@session.time_zone, 'America/New_York') as Today_CTZ;

This will return 3 columns, and it should show yestderday's date, todays date, and todays date. This is what is returned instead.

yesterday	today	Today_CTZ
2023-01-11	2023-01-12	2023-01-11 19:00:00

It has returned yesterdays date, todays date, and yesterdays date.

This query was run on 1/12/23 at 9:20 AM

This is what it should produce

yesterday	today	Today_CTZ
2023-01-11	2023-01-12	2023-01-12 19:00:00

Why is it doing this?

edited Jan 12 '23 at 15:39

asked Jan 12 '23 at 15:37

SuspectT3

1

What is your session timezone? – Barmar Jan 12 '23 at 15:56
@Barmar central time, though the issue is the same using @@global.time_zone – SuspectT3 Jan 12 '23 at 19:26
central time is only 1 hour different from eastern time, why do you expect the time to be 19:00:00? – Barmar Jan 12 '23 at 19:28
@barmar, that's a valid point but not the issue. the issue, is that convert_tz is pulling 24 hours behind the date it should – SuspectT3 Jan 17 '23 at 18:38
If the session time zone is 5 hours ahead of New York, converting the time zone subtracts 5 hours, which goes to 19:00 of the previous day. – Barmar Jan 17 '23 at 19:45
You'd get this result if the session time zone is GMT, for instance. – Barmar Jan 17 '23 at 19:46

0 Answers0