Need to convert UTC to EST time in SQL Query

[Gopi]

Hi all,

In this query hear I need to convert the CURRENT_TIMESTAMP UTC time to EST time while inserting the record into the table. Can someone suggest how I can achieve this?

{"memberOf":["INSERT INTO PS_EAP_SUB_IN (EMPLID, OPRID, ROLENAME, EAP_TRANSACTIONID, EAP_ACTION, EAP_STATE, EAP_RETURN_VAL, LASTUPDDTTM) VALUES ('${user.employeeid}', UPPER('${user.systemUserName}'), '${task.entitlement_valueKey.entitlement_value}', (SELECT MAX(EAP_TRANSACTIONID) + 1 FROM PS_EAP_SUB_IN), 'I', 'A', ' ', CURRENT_TIMESTAMP)"]}

Regards,

Gopi.

[sudeshjaiswal]

Hello @Gopi

You can use,
CONVERT_TZ(current_timestamp,'+00:00','-04:00').

and if you want to format the date you may use:
DATE_FORMAT(CONVERT_TZ(current_timestamp,'+00:00','-04:00'),'%Y-%m-%d %H:%i:%s')

PFA

Thanks,

[Gopi]

Hi @sudeshjaiswal ,

Thanks for your response. The syntax which you suggested looks like MYSQL syntax is not working it says invalid identifier error and I'm inserting this into Oracle Database maybe that's why it's not accepting. 

Regards,

Gopi.

[dgandhi · Accepted solution]

Have you tried below?

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

[rushikeshvartak]

Please refer https://community.oracle.com/customerconnect/discussion/614383/converting-utc-to-est-time-zone-in-lo...