CODEWITHSUNDEEP
oracle11g
JavaSheriff
JavaSheriff

Reputation: 7665

Oracle - Convert and display sysdate in EST

Using oracle 11 , how to Convert and display sysdate in EST?
Server is in PST location

I tried this:

select from_tz(CAST(sysdate AS TIMESTAMP),'EST') from dual
/

I don't get correct results..

Upvotes: 0

Views: 746

Answers (3)

MT0
MT0

Reputation: 167991

If you really want to use SYSDATE then you can use:

SELECT from_tz(CAST(sysdate AS TIMESTAMP),'PST') AT TIME ZONE 'EST5EDT' FROM DUAL;

Which outputs:

| FROM_TZ(CAST(SYSDATEASTIMESTAMP),'PST')ATTIMEZONE'EST5EDT' |
| :--------------------------------------------------------- |
| 02-APR-21 11.07.33.000000 PM EST5EDT                       |

or:

SELECT from_tz(CAST(sysdate AS TIMESTAMP),'US/Pacific') AT TIME ZONE 'US/Eastern' FROM DUAL;

Which outputs:

| FROM_TZ(CAST(SYSDATEASTIMESTAMP),'US/PACIFIC')ATTIMEZONE'US/EASTERN' |
| :------------------------------------------------------------------- |
| 02-APR-21 11.07.33.000000 PM US/EASTERN                              |

db<>fiddle here

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191275

Use systimestamp rather than sysdate, because it is already timezone-aware; then at time zone to translate:

select systimestamp at time zone 'America/New_York' from dual

Using a region is better/safer than an abbreviation like 'EST", which might not be unique; and gives you the wrong answer - this shows mine, Justin's alternative (and maybe better!) region, what you get with EST, and all the things EST is an abbreviation of. (You could use EST5EDT, but a region is still clearer.)

You could also set your session to an East-coat region (if it isn't already) and then use current_timestamp instead of systimestamp:

alter session set time_zone = 'America/New_York';
select current_timestamp from dual;

db<>fiddle

Either way, how it is displayed is down to your client/application - e.g. the session nls_timestamp_tz_format - unless you convert it to a string with to_char(), supplying the format you want.

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231671

Use systimestamp since that includes a time zone.

select systimestamp at time zone 'US/Eastern'
  from dual;

should return a timestamp in the Eastern time zone (assuming your database time zone files are up to date).

Note that if you ask for a timestamp in EST, that should be an hour earlier than the current time in the Eastern time zone of the United States because the US is in Daylight Savings Time. So the Eastern time zone is in EDT currently not EST.

Upvotes: 2

Related Questions