Need to convert any timezone to GMT time zone in oracle

Question

Query condition is like below:

creation_date >=last_success_run_date

here creation date is coming like sometime PDT(Pacific Daylight Time) or sometime PST (Pacific Standard Time) format.

and last_success_run_date is always GMT format.

So, I want to know if is there any oracle function available which will convert any timezone format to GMT timezone format.

Zaynul Abadin Tuhin · Answer 1 · 2019-11-05T09:39:04.423

0

use new_time() function is used to convert a date from timezone1 to a date in timezone2.

NEW_TIME(date, timezone1, timezone2)

NEW_TIME(date, 'PDT', 'GMT')

edited Nov 05 '19 at 09:39

answered Nov 05 '19 at 06:30

Zaynul Abadin Tuhin

31,407
5
33
63

we can't use new_time function here as we don't know time format will be PST or PDT – nodev_101 Nov 05 '19 at 07:50
@send that in parameter – Zaynul Abadin Tuhin Nov 05 '19 at 07:51

score 0 · Answer 2 · answered Nov 05 '19 at 08:16

Try this one:

FROM_TZ(creation_date, 'America/Los_Angeles') AT TIME ZONE 'UTC'

or

SYS_EXTRACT_UTC(FROM_TZ(creation_date, 'America/Los_Angeles'))

If creation_date is a DATE rather than TIMESTAMP data type then you have to use CAST(creation_date AS TIMESTAMP) since FROM_TZ accepts only only TIMESTAMP datatypes as input value.

Need to convert any timezone to GMT time zone in oracle

2 Answers2