Home » SQL & PL/SQL » SQL & PL/SQL » How Do I get 'GMT' string as part of time zone format
How Do I get 'GMT' string as part of time zone format [message #272594] Fri, 05 October 2007 12:23 Go to next message
pkirangi
Messages: 74
Registered: August 2005
Member
Hello All,
I have a date variable, and I need to get timezone in the format.
Something like 12-AUG-2007 13:02 54 GMT
Our dbtime zone is +00:00 meaning GMT.

I tried using:
select to_char(CAST(sysdate AS TIMESTAMP),'dd-mon-yyyy hh:mi:SS TZR') from dual

but it gives me the following
05-OCT-2007 05:20:57 +00:00

I need something like
05-OCT-2007 05:20:57 GMT.

Also does a date variable store timezone information?

Thanks
PHK.




Re: How Do I get 'GMT' string as part of time zone format [message #272596 is a reply to message #272594] Fri, 05 October 2007 12:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle uses dbtimezone to display your region.
If your database was created with '+00:00' as time zone then it displays this.
You should to create the database with 'GMT' instead.

If you don't have any TIMESTAMP WITH LOCAL TIME ZONE columns then you can use ALTER DATABASE to change your time zone.
SQL> select dbtimezone from dual;
DBTIME
------
+01:00

1 row selected.

Regards
Michel

[Updated on: Fri, 05 October 2007 12:43]

Report message to a moderator

Re: How Do I get 'GMT' string as part of time zone format [message #273047 is a reply to message #272594] Mon, 08 October 2007 22:58 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member

Try it this way ...

select
  to_char( systimestamp at time zone 'GMT', 'dd-mon-yyyy hh:mi:SS TZR' ) result
from dual ;

RESULT
--------------------------------------------------------------
09-oct-2007 03:47:04 GMT



or this way.

alter session set NLS_TIMESTAMP_TZ_FORMAT = 'dd-mon-yyyy hh:mi:SS TZR' ;

select
  systimestamp at time zone 'GMT' result
from dual ;

RESULT
---------------------------------------------------------------------------
09-oct-2007 03:54:09 GMT



Also, consider using either 'hh24:mi:ss' or 'hh:mi:ss am' for your time format to avoid the ambiguity that 'hh:mi:ss' alone will inevitably produce.

--
Joe Fuda
SQL Snippets

Re: How Do I get 'GMT' string as part of time zone format [message #273049 is a reply to message #272594] Mon, 08 October 2007 23:05 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member

Re.
Quote:

"also does a date variable store timezone information?"

... nope, DATE variables do not have time zone information. The only data type that stores time zone information is TIMESTAMP WITH TIME ZONE. Time zone information can also be determined for the TIMESTAMP WITH LOCAL TIME ZONE data type, but the time zone info. is not actually stored as part of the column data.

--
Joe Fuda
SQL Snippets

Re: How Do I get 'GMT' string as part of time zone format [message #273462 is a reply to message #272594] Wed, 10 October 2007 09:55 Go to previous messageGo to next message
pkirangi
Messages: 74
Registered: August 2005
Member
Thanks Joe,
But looks like if I dont have to hardcode 'GMT' anywhere(Our db servers are set in GMT time, and the time zone has been set to +00:00), I woull have to alter database to set the time zone format as GMT instead of +00:00.

This is what I eventually used:
alter database set time_zone='GMT';

select to_char((from_tz(CAST(sysdate AS TIMESTAMP),dbtimezone)),'DD-MON-YYYY HH24:MI:SS TZR') from dual;

would yeild:
10-OCT-2007 14:50:15 GMT

Thanks
PHK

Re: How Do I get 'GMT' string as part of time zone format [message #273486 is a reply to message #273462] Wed, 10 October 2007 11:09 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Sure, that works too. FYI, now that you've updated your database's timezone you could do it this way

select to_char( systimestamp at time zone dbtimezone, 'DD-MON-YYYY HH24:MI:SS TZR') from dual ;

which would save you from calling CAST and FROM_TZ.

(Note that systimestamp returns a TIMESTAMP WITH TIME ZONE value.)

--
Joe Fuda
SQL Snippets
Re: How Do I get 'GMT' string as part of time zone format [message #273527 is a reply to message #272594] Wed, 10 October 2007 14:46 Go to previous message
pkirangi
Messages: 74
Registered: August 2005
Member
Thats another alternative.
Thanks Joe.

- PHK
Previous Topic: Question about rowid when querying using database link
Next Topic: Errors (ORA-03113, ORA-03114) for trigger and deref()
Goto Forum:
  


Current Time: Wed Dec 07 16:38:26 CST 2016

Total time taken to generate the page: 0.16534 seconds