Click HERE to see how Saviynt Intelligence is transforming the industry. |
04/25/2024 05:17 AM
Hi Team,
Can you please let me know if we can get the timestamp present in the saviynt reports in EST instead of UTC?
Regards,
Sharada
Solved! Go to Solution.
04/25/2024 05:51 AM
@Sharada you can try below
Run Summary For Analytics Report - ${analyticsConfig.analyticsName} Run @ ${new java.text.SimpleDateFormat('yyyy-MM-dd HH:mm:ss').format(new java.util.Date(System.currentTimeMillis() + java.util.TimeZone.getTimeZone('America/New_York').getOffset(System.currentTimeMillis())))} By Admin
04/25/2024 05:56 AM
Thanks for the reply, I will check and let u know
04/25/2024 06:04 AM
Hi,
Just had a query. Should I add the below lines in the analytic query?
${new java.text.SimpleDateFormat('yyyy-MM-dd HH:mm:ss').format(new java.util.Date(System.currentTimeMillis() + java.util.TimeZone.getTimeZone('America/New_York').getOffset(System.currentTimeMillis())))}
Regards,
Sharada
04/25/2024 06:08 AM
@Sharada may u need for query or or priting in subject line or body?
04/25/2024 06:37 AM
Hi,
We have an analytic which gives us a list of users who were terminated in the last 8 days. The termination timestamp in the report is in UTC. We want this timestamp to be in EST
04/25/2024 07:01 AM
@Hi @Sharada ,
SELECT
u.username AS 'Username',
u.FIRSTNAME AS 'First Name',
u.LASTNAME AS 'Last Name',
CONVERT_TZ(u.TERMDATE,'UTC','EST') AS 'Termination Date',
u1.username AS 'Manager',
CASE
WHEN u.statuskey IS NULL THEN 'Not Available'
WHEN u.statuskey = 0 THEN 'Inactive'
ELSE u1.statuskey
END 'User Status'
FROM
users u,
users u1
WHERE
u.TERMDATE >= DATE_SUB(CURDATE(), INTERVAL 8 DAY)
and u.statuskey = 0
and u1.userkey = u.manager;
If this helps your request, please consider selecting Accept As Solution and hit Kudos
04/25/2024 07:55 PM
SELECT
concat(
'Report Extracted On EST ',
CONVERT_TZ(now(), 'UTC', 'EST')
) AS module
FROM
dual
UNION
SELECT
concat(
'Report Extracted On IST ',
CONVERT_TZ(now(), 'UTC', '+05:30')
) AS module FROM
dual
04/25/2024 06:14 AM - edited 04/25/2024 06:16 AM
@Sharada use like below
select startdate as UTC,CONVERT_TZ(startdate,'UTC','EST') as EST from users where username=931813
04/29/2024 10:03 PM
Thank you all for the reply. The convert_TZ function is not working as expected. It is not considering the daylight savings. Th function is subtracting 5 hours from UTC time to convert to EST, but it should subtract 4 hours because of daylight saving. Is there any other way?
04/29/2024 10:18 PM - edited 04/29/2024 10:19 PM
SELECT
CONCAT('Report Extracted On EST ', FROM_UNIXTIME(UNIX_TIMESTAMP(now()) - 14400, '%Y-%m-%d %H:%i:%s')) AS module
FROM
dual
OR
SELECT
CONCAT('Report Extracted On EDT ', CONVERT_TZ(now(), 'UTC', 'America/New_York')) AS module
FROM
dual;
05/02/2024 02:14 AM
Thank you @rushikeshvartak . It worked with the above query.