native
September 16, 2001 - 11am Central time zone
Reviewer: Chen from USA
What do you mean by time zone support is native, what is the other kind of support.
Followup September 16, 2001 - 3pm Central time zone:
In order to do timezones in 8i and before, you have to do it yourself. You can use the NEW_TIME
builtin but you must not the timezone you are in and so on. The DATE datatype has no timezone
component.
In 9i there is a datatype "timestamp with timezone". The timestamp can store the date, time (down
to 9 decimal places with seconds), and the TIMEZONE. The database "understands" timezones so if
you get the difference between 1pm PST and 4pm EST it understands that it is in fact the same time.
And so on.
In short, 9i supports and is timezone aware. 8i and before -- not so.
October 2, 2001 - 6pm Central time zone
Reviewer: A reader from USA
August 24, 2003 - 7am Central time zone
Reviewer: A reader
Hi Tom,
We have 4 production Oracle instance on one box.One of the instance we need to set Time zone to
UTC (GMT..). I came to know there is Solaris envt. variable called $TZ which help us to do this.
How this variable works in solaris?
Time zone set by this variable is session level?
Please elobarate and advice.
Thanks in advance
Followup August 24, 2003 - 11am Central time zone:
the TZ environment variable is picked up by the dedicated or shared server. these guys inherit
their environment from whomever starts them.
So, as long as
a) the listener has tz set
b) the guy who starts the database has tz set
c) everyone connects via the listener
you'll be OK. direct connections (no listener) will fork and exec their own dedicated servers --
so they will get the TZ of the client.
August 24, 2003 - 12pm Central time zone
Reviewer: A reader
Hi Tom,
Thnaks for the reply
You mention
a) the listener has tz set
b) the guy who starts the database has tz set
Could you please explain above statements ?
Thnaks
Followup August 24, 2003 - 4pm Central time zone:
a) when you start the listener, make sure you've done something like:
export TZ=GMT
lsnrctl start
make sure the listeners environment has the TZ you desire to use
b) see (a), change listener to database.
August 24, 2003 - 1pm Central time zone
Reviewer: A reader
Hi Tom,
How can we set Timezone for Listener Process?
Thnaks
Followup August 24, 2003 - 4pm Central time zone:
by setting the environment, like you would for anything on unix
August 25, 2003 - 9am Central time zone
Reviewer: A reader
Hi Tom
Here is our Scenerio.
We have our prod DB server on EST timezone. We set one Oracle instance on GMT by seeting $TZ unix
envt. variable.
I restarted oracle instance and ran below query.
SQL> select sessiontimezone,dbtimezone from dual;
SESSION DBTIMEZ
------- -------
-04:00 +00:00
SQL> select sysdate from dual;
SYSDATE
--------------------
25-AUG-2003 09:29:54
-- --------------------
if i run this (select sysdate from dual)I want SYSDATE in GMT. I am not geeting in GMT eventhogh
I've set $TZ for the instacne to GMT.
Please advice
Thnaks in advance
Followup August 25, 2003 - 9am Central time zone:
umm, change your session timezone perhaps?
given I have no clue what time gmt was when you did this -- really, I cannot tell if sysdate is
right, wrong, made up or what.
I've no idea what steps you went through here.
maybe you can cut and paste something like this:
[ora920@localhost ora920]$ export TZ=GMT
[ora920@localhost ora920]$ date
Mon Aug 25 09:55:17 EDT 2003
[ora920@localhost ora920]$ sysdba
SQL*Plus: Release 9.2.0.3.0 - Production on Mon Aug 25 13:55:22 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
idle> startup
ORACLE instance started.
Total System Global Area 168890964 bytes
Fixed Size 451156 bytes
Variable Size 150994944 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
idle> @bigdate
Session altered.
idle> select sysdate from dual;
SYSDATE
--------------------
25-aug-2003 13:55:42
worked dandy for me.
August 25, 2003 - 1pm Central time zone
Reviewer: A reader
Hi Tom,
Thnaks for the reply.
How to change session time zone ?We are using oracle 8.1.7.3.
Thanks
Followup August 26, 2003 - 7am Central time zone:
you do not in 8i, did not exist until 9i.
i had to assume from the prior reviews that the db was 9i as it was using the new timezone
functions for 9i.
in 8i, the TZ environment variable does it all.
Here's how you can do it in PL/SQL
December 30, 2003 - 1pm Central time zone
Reviewer: Jim Nasby from Austin, TX
CREATE OR REPLACE PACKAGE time AS
c_epoch_tz CONSTANT timestamp with time zone := to_timestamp_tz('1970-01-01 GMT', 'YYYY-MM-DD
TZD');
c_epoch CONSTANT timestamp := to_timestamp('1970-01-01', 'YYYY-MM-DD');
FUNCTION t_s(time_in timestamp with time zone) RETURN int;
FUNCTION t_ms(time_in timestamp with time zone) RETURN number;
FUNCTION s_t(time_in int) RETURN timestamp;
FUNCTION ms_t(time_in number) RETURN timestamp;
FUNCTION s_tz(time_in int) RETURN timestamp with time zone;
FUNCTION ms_tz(time_in number) RETURN timestamp with time zone;
FUNCTION epoch RETURN timestamp;
FUNCTION epoch_tz RETURN timestamp with time zone;
END time;
/
show err
CREATE OR REPLACE PACKAGE BODY time AS
FUNCTION t_s(time_in timestamp with time zone) RETURN int
AS
diff interval day(9) to second(0) := time_in at time zone 'GMT' - c_epoch_tz;
BEGIN
RETURN extract(day from diff) * 86400 + extract(hour from diff) * 3600 + extract(second
from diff);
END;
FUNCTION t_ms(time_in timestamp with time zone) RETURN number
AS
diff interval day(9) to second(0) := time_in at time zone 'GMT' - c_epoch_tz;
BEGIN
RETURN extract(day from diff) * 86400000 + extract(hour from diff) * 3600000 +
extract(second from diff) * 1000;
END;
FUNCTION s_t(time_in int) RETURN timestamp AS
BEGIN
RETURN c_epoch + time_in/86400;
END;
FUNCTION ms_t(time_in number) RETURN timestamp AS
BEGIN
RETURN c_epoch + time_in/86400000;
END;
FUNCTION s_tz(time_in int) RETURN timestamp with time zone AS
BEGIN
RETURN c_epoch_tz + time_in/86400;
END;
FUNCTION ms_tz(time_in number) RETURN timestamp with time zone AS
BEGIN
RETURN c_epoch_tz + time_in/86400000;
END;
FUNCTION epoch RETURN timestamp AS
BEGIN
RETURN c_epoch;
END;
FUNCTION epoch_tz RETURN timestamp with time zone AS
BEGIN
RETURN c_epoch_tz;
END;
END time;
/
show err
Followup December 30, 2003 - 2pm Central time zone:
I would not encourage or even rely on that. true, there is a bit of the TZ supported undocumented
that got slipped into 8iR3 BUT there are tons of known (and never to be fixed) issues.
Undocumented
Not supported
Caveat Emptor
December 30, 2003 - 6pm Central time zone
Reviewer: Jim Nasby from Austin, TX
Oops, sorry, should have made it clear that that was a 9i only solution (and probably not even the
best one but this thread was about the closest I found when trying to figure out how to do it in
9i).
Followup December 30, 2003 - 6pm Central time zone:
:)
it's ok then
Wiping egg off face
December 31, 2003 - 2pm Central time zone
Reviewer: Jim Nasby from Austin, TX
Looks like I didn't quite test things enough; the seconds precision of 0 breaks the millisecond
functions and I also should have added deterministic and parallel clauses.
Corrected code can be found at http://jim.nasby.net/scripts/time.sql
ORA-01857: not a valid time zone
October 18, 2004 - 7am Central time zone
Reviewer: Thiyag from Chennai, TN India
Hi Tom,
I have a shell script which sets the TZ parameter in unix and connects to the sqlplus. The
following is the snapshot of the script:
export TZ=EST17EDT
date
sqlplus -S << !
<userid>/<password>
select to_char(sysdate,'YYYY/MM/DD-HH24:MI:SS') from dual;
exit;
!
We had Oracle 8.1.5 where the above script worked fine. Recently we migrated to 8.1.7 (both
instances are on the same unix box) and it started giving the following errors:
ERROR:
ORA-01857: not a valid time zone
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}] | [INTERNAL]
where <logon> ::= <username>[/<password>][@<connect_string>] | /
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}] | [INTERNAL]
where <logon> ::= <username>[/<password>][@<connect_string>] | /
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
ERROR IN RETRIEVAL PROCESSING
Can you please share your insights and let me know what could be the source of the problem?
Followup October 18, 2004 - 9am Central time zone:
17? what is 17 in there?
i cannot find any valid use of EST17EDT -- EST1EDT..EST8EDT are valid, but i've not ever seen 17
unix time
March 2, 2005 - 10am Central time zone
Reviewer: Mariano from Cordoba, Argentina
Hi, Tom.
Is there any builtin function that retrieves the date (dd/mm/yyyy hh24:mi:ss) when I input a
standard Unix time field (you know, number of seconds since 01/01/1970)?
Regards.
Mariano.-
SYSDATE to UNIX
January 4, 2007 - 4pm Central time zone
Reviewer: Joe
How do you convert SYSDATE into UNIX time? I found this online, however SQL Developer doesnt always pull back a value, unless you execute it like 5 times.
SELECT (SYSDATE - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) AS dt FROM dual
Date arithmathics and Timezone
December 21, 2007 - 9am Central time zone
Reviewer: Mette Stephansen from Denmark
Hi there !
We have some people who have stored the date in java-timeformat (ms since 1970).
I use this sql to convert from that into a normal date (taking care of timezones):
SELECT
CAST(
FROM_TZ(
TO_timestamp ('19700101000000', 'YYYYMMDDHH24MISS') + NUMTODSINTERVAL (1198249252000 / 1000 ,
'SECOND')
, 'GMT') AT TIME ZONE sessiontimezone
AS timestamp
) "dato i dk"
FROM dual;
----
So far so good !
But how do I create that java-date again, taking care of various timezones. This one will create a
time one hour ahead of my local time (which is GMT +1).
SELECT (SYSDATE - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) * 1000 AS dt FROM dual
Pls. give me a hint - I'm running in cirle now trying to make it work, tyring to user "at local
timezone" etc.
best regards from
Mette
Followup December 21, 2007 - 3pm Central time zone:
... java-timeformat....
Oh no you don't, java can steal lots of things - java-ites can claim many things, but you are not going to steal what is rightly a UNIX thing.
I'd ask the java programmers what to do with this, I'm not aware of a java specific date'ism that would need to be dealt with?
if you have a date in gmt and want to convert it to some other timezone, one of the easiest ways is...
http://asktom.oracle.com/tkyte/Misc/CTime.html
Date arithmathics and Timezone
December 21, 2007 - 4pm Central time zone
Reviewer: Michel Cadot from France
The problem is that new_time only accepts few time zones and none from Europe (but GMT).
Thus this king of complex formula.
Regards
Michel
Followup December 22, 2007 - 11am Central time zone:
use timestamps for "more" TZ support then...
ops$tkyte%ORA10GR2> select from_tz( to_timestamp( '10am', 'hham' ), 'UTC' ),
from_tz(to_timestamp('10am','hham'),'UTC') at time zone 'EST' from dual;
FROM_TZ(TO_TIMESTAMP('10AM','HHAM'),'UTC')
---------------------------------------------------------------------------
FROM_TZ(TO_TIMESTAMP('10AM','HHAM'),'UTC')ATTIMEZONE'EST'
---------------------------------------------------------------------------
01-DEC-07 10.00.00.000000000 AM UTC
01-DEC-07 05.00.00.000000000 AM EST
I did read the Ctime.html docs :-)
December 21, 2007 - 6pm Central time zone
Reviewer: Mette from DK Denmark
Sorry for confusing Java time with Unix time though. Never really worked with either of the
tools/OS'.
I have read the doc Tom refers to, but does not help me for the more generic solution (that it
takes whatever timezone I'm in and makes the correct unix time).
But again ... the java program (some freeware Quartz scheduler) stores the next-schedule-time in
Oracle as this Unix time. And from other programs we would like to read (&write) into this system,
with correct timezones as I mentioned. We in DK are in GMT+1 - and the latter SQL gives me the
Unix time in GMT (Of course I could just add 1 hour to the stuff, I know - but I would like to have
it like the Unix2Oracle taking my local timezone into consideration (we might have consultants from
UK, Bulgaria etc accessing the system as well ... and their TZ migth be different from tge GMT+1).
Så thats why I need to be able to generate the Unix time from ie Sysdate correct.
best regards
Mette
Followup December 22, 2007 - 11am Central time zone:
sorry, I just don't know what format you need - what would be the "correct" unix time you are looking for??
To convert it back again !
December 22, 2007 - 2pm Central time zone
Reviewer: Mette from DK
The first SQL i sent translates the unix time in the field to the oracle date time, according to my
timezone.
Now I just need to be able to to the reverse thing.
best regards
Mette
Followup December 22, 2007 - 4pm Central time zone:
then just "at time zone" yours back to UTC, convert to a date, and subtract jan 1 1970 from it, multiply by 24*60*60??
eg: just do the reverse.
s$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> SELECT (SYSDATE - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) * 1000 AS dt1
FROM dual;
DT1
----------------
1198340473000
ops$tkyte%ORA10GR2> SELECT ( cast((systimestamp at time zone 'UTC') as date) -
to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) * 1000 AS dt2 FROM dual;
DT2
----------------
1198358473000
Thank you very much!
December 22, 2007 - 5pm Central time zone
Reviewer: Mette Stephansen from DK
Tom, now it works:
To Unix_time:
SELECT
(cast((to_timestamp('21-12-2007 16:00:52','DD-MM-YYYY HH24:MI:SS')) at time zone 'GMT' as date) -
to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) * 1000 unix_time
FROM dual;
From Unix_time:
SELECT
CAST(FROM_TZ(TO_timestamp ('19700101000000', 'YYYYMMDDHH24MISS') + NUMTODSINTERVAL
(1198249252000 / 1000 ,'SECOND'), 'GMT')
AT TIME ZONE sessiontimezone
AS timestamp ) "dato i dk"
FROM dual;
This was what I was looking for, since we sometimes have to update the schedule times manualy in
Oracle.
I simply lost touch of where to add the "at timezone stuff" - running around in circles :-)
Have a nice Christmas
Mette
TimeZone
December 19, 2009 - 5am Central time zone
Reviewer: D.Sasi Kumar from India
Hi,
The above query worked well. Thanks for posting this. Regaring with the followup, I need one more
clarification. A database is started with One TZ and Listener is started with One TZ. So, what will
be behaviour of the client connection, whether it will inherit Listener TZ or database TZ. If
anybody faced such situation, kindly advice
February 3, 2010 - 8am Central time zone
Reviewer: reader
I use the following to convert timestamp to seconds (unix time).
select extract(day from (systimestamp - to_date('01011970', 'ddmmyyyy'))) * 24 * 3600 +
extract(hour from (systimestamp - to_date('01011970', 'ddmmyyyy'))) * 3600 +
extract(minute from (systimestamp - to_date('01011970', 'ddmmyyyy'))) * 60 +
extract(second from (systimestamp - to_date('01011970', 'ddmmyyyy'))) timestap_to_secs
from dual;
Is there any way to convert timestamp to milliseconds?
Followup February 3, 2010 - 10am Central time zone:
unix time is the number of seconds, not milliseconds.
so, I don't know what to tell you since the unix time you talk of, the number of seconds since 1-jan-1970, doesn't do that.
what are you looking for?
|