How Do I get 'GMT' string as part of time zone format [message #272594] |
Fri, 05 October 2007 12:23  |
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   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
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   |
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   |
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   |
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
|
|
|
|