What do you mean by time zone support is native, what is the other kind of support.

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

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

Hi Tom,

How can we set Timezone for Listener Process?

Thnaks

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
 

Hi Tom,

Thnaks for the reply.

How to change session time zone ?We are using oracle 8.1.7.3.

Thanks

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

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).

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 </code> http://jim.nasby.net/scripts/time.sql <code>

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?

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.-

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

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

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

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

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

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

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

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

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

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?

Like the previous reviewer I also need to retrieve a timestamp field as a number including milliseconds.

As I understand it Oracle stores timestamp fields internally as the number of seconds from the epoch date 1/1/1970, and then represents that number as a date when queried.
What I need to do is store a date with time including milliseconds in a timestamp field but when queried I want to return the internal number representation to our application.
Also when the application loads data it will have the date time (with milliseconds) as a number, so I need to convert it back to an oracle timestamp before doing the insert.

Please advise on how best to achieve that.

Also the link http://asktom.oracle.com/tkyte/Misc/CTime.html does not work anymore. I get a 404 error. Please can you post the new location of this document.

Many thanks

Ok thanks I didn't realise that it was stored internally that way.

As for the application..it is beyond my control. They have asked for the timestamp to be in that format so that they can do time series analysis using the epoch format.

They want to store it in the database as a number also but I don't believe this is a good idea. I would prefer to have it as a timestamp in the database and just convert it to the epoch format when the application uses it.

Dates should be stored as dates right? How the application sees the format of that date is up to them, but storing it as a date is important for future use, who knows who will use that data in the future...am I on the right track?

If you need not to index or perform time-related calculations on your timestamp column it may be a not so bad idea to store it in the epoch format. It is at least unambiguous. The Oracle implementation of date/timestamp is a subject to various issues and bugs related to the timezone processing (e.g. bug 10132189).

So the answer to your question is as (almost always "it depends"). All that said - an usage of non-date/timestamp format to store "points in time" should be considered as an exception anyway.

"you are on the right track, and you know how to retrieve the data for them now (right?) ...."


I think so but if you could provide a working example that would be very helpful.