Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon

To receive timestamp saviynt reports in est instead of UTC

Sharada
Regular Contributor
Regular Contributor

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

11 REPLIES 11

Raghu
All-Star
All-Star

@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


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

Sharada
Regular Contributor
Regular Contributor

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

Sharada
Regular Contributor
Regular Contributor

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

 

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


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

Sharada
Regular Contributor
Regular Contributor

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

@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_0-1714100044256.png

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

 


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

Raghu
All-Star
All-Star

@Sharada  use like below

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

CR_0-1714050843951.png

 

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

 

 

 


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

Sharada
Regular Contributor
Regular Contributor

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?  

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;


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

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