Menu
I need to convert the sysdate and time to a particular timezone like EST. I can't assume my current time zone.
How to convert this in plsql? Please help me.
374
I just ran select systimestamp from dual; in SQL developer on my machine, it replied with 30-MAY-16 12.49. 28.279000000 PM -05:00 . The -05:00 shows the timezone offset (5 hours behind UTC, essentially 5 hours behind GMT with no daylight savings time adjustments made to GMT). This shows the database server time zone.
Best Answer. SELECT SYSTIMESTAMP AS Phoenix_TZ ,CAST(SYSTIMESTAMP AT TIME ZONE 'America/New_York' AS TIMESTAMP WITH TIME ZONE) AS NY_TZ FROM dual; Hope this helps.
Use the ALTER DATABASE SET TIME_ZONE command to change the time zone of a database. This command takes either a named region such as America/Los_Angeles or an absolute offset from UTC. This example sets the time zone to UTC: ALTER DATABASE SET TIME_ZONE = '+00:00';
Assuming you have a TIMESTAMP WITH TIME ZONE (such as systimestamp), you can use the AT TIME ZONE syntax. For example, I can take the current systimestamp and convert it to UTC (GMT), Eastern, and Pacific time zones by specifying different time zone names.
SQL> ed
Wrote file afiedt.buf
1 select systimestamp at time zone 'UTC' current_time_in_utc,
2 systimestamp at time zone 'Us/Eastern' current_time_in_est,
3 systimestamp at time zone 'US/Pacific' current_time_in_pst
4* from dual
SQL> /
CURRENT_TIME_IN_UTC
---------------------------------------------------------------------------
CURRENT_TIME_IN_EST
---------------------------------------------------------------------------
CURRENT_TIME_IN_PST
---------------------------------------------------------------------------
26-APR-12 05.36.11.802000 PM UTC
26-APR-12 01.36.11.802000 PM US/EASTERN
26-APR-12 10.36.11.802000 AM US/PACIFIC
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
