To receive timestamp saviynt reports in est instead of UTC

[Sharada]

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

[Raghu]

@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

[Sharada]

Thanks for the reply, I will check and let u know

[Sharada]

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

[Raghu]

@Sharada  may u need for query or or priting in subject line or body?

[Sharada]

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

[PremMahadikar]

@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

[rushikeshvartak]

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

[Raghu]

@Sharada  use like below

select startdate as UTC,CONVERT_TZ(startdate,'UTC','EST') as EST from users where username=931813

https://forums.saviynt.com/t5/identity-governance/need-to-convert-utc-to-est-time-in-sql-query/m-p/3...

[Sharada]

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?  

[rushikeshvartak · Accepted solution]

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;

[Sharada]

Thank you @rushikeshvartak . It worked with the above query.