Converting from UTC Time to CST TIme

[h_sapkota]

Hi Team,

We have a requirement where we need to compare time in the CST Time zone. We tried to use the function CONVERT_TZ(sysdate(), 'UTC', 'America/Chicago') but Saviynt is not returning any values for the function.

We have used below query to try to incorporate the Day Light Savings as well but it will only work for the year 2024.

CASE
WHEN SYSDATE() BETWEEN
(
SELECT CONCAT_WS('-', YEAR(SYSDATE()), '03', 1 + 7 + 7 - DAYOFWEEK(CONCAT_WS('-', YEAR(SYSDATE()), '03', '01')))
)
AND
(
SELECT CONCAT_WS('-', YEAR(SYSDATE()), '11', 1 + 7 - DAYOFWEEK(CONCAT_WS('-', YEAR(SYSDATE()), '11', '01')))
)
THEN SYSDATE() - INTERVAL 5 HOUR
ELSE SYSDATE() - INTERVAL 6 HOUR
END

Can anyone help us what can be best solution or approach to compare dates: Below is the query where we are using this:

SELECT u1.username, '0' AS stats
FROM users u1
WHERE
u1.statuskey = '1'
AND u1.customproperty15 = 'YES'
AND TRIM(u1.username) != ''
AND u1.username IS NOT NULL
AND u1.employeetype IN ('Employee', 'Agency Employee')
AND DATE_ADD(u1.enddate, INTERVAL 1 DAY) <=
(CASE
WHEN SYSDATE() BETWEEN
(
SELECT CONCAT_WS('-', YEAR(SYSDATE()), '03', 1 + 7 + 7 - DAYOFWEEK(CONCAT_WS('-', YEAR(SYSDATE()), '03', '01')))
)
AND
(
SELECT CONCAT_WS('-', YEAR(SYSDATE()), '11', 1 + 7 - DAYOFWEEK(CONCAT_WS('-', YEAR(SYSDATE()), '11', '01')))
)
THEN SYSDATE() - INTERVAL 5 HOUR
ELSE SYSDATE() - INTERVAL 6 HOUR
END)
LIMIT 999;

[CR]

can you try below

CAST(FROM_TZ(CAST(DATE_FIELD AS TIMESTAMP), 'UTC')
at time zone 'America/New_York' AS Date) as DA_NAME

[rushikeshvartak]

SELECT u1.username,
       '0' AS stats
FROM   users u1
WHERE  u1.statuskey = '1'
       AND u1.customproperty15 = 'YES'
       AND Trim(u1.username) != ''
       AND u1.username IS NOT NULL
       AND u1.employeetype IN ( 'Employee', 'Agency Employee' )
       AND Date_add(u1.enddate, INTERVAL 1 day) <=
           Convert_tz(Sysdate(), 'UTC', 'America/Chicago')
LIMIT  999; 

[h_sapkota]

Hi @rushikeshvartak ,

The provided function is not returning any values in our Saviynt EIC 23.5.

Plus below queries return nothing in results:
select * from mysql.time_zone_transition_type;
select * from mysql.time_zone_transition;
select * from mysql.time_zone_name;
select * from mysql.time_zone_leap_second;
select * from mysql.time_zone;

Can you let us know how can we configure our MYSQL to support the above function like in your case!

Regards,
Hitesh Sapkota

[rushikeshvartak]

===

works in 24.2