date differences between database and server

From: Caja <carsten.jacobs_at_web.de>
Date: 25 Jan 2006 04:00:53 -0800
Message-ID: <1138190453.876430.94130@f14g2000cwb.googlegroups.com>

Hi all,

I ran into the following date problem:
I have the value 1137452400 which are the seconds from 01.01.1970 00:00:00 to 17.01.2006 00:00:00 (unix epoche).

When I reconvert this value on a Unix-system (IBM AIX 5.2) to a date, I get the right date 17.01.2006 00:00:00.
$ /usr/bin/perl -le 'print scalar localtime(shift);' 1137452400

When I convert this value in sqlplus I get 16.01.2006 23:00:00. sqlplus> select to_date( '01.01.1970 00:00:00', 'dd.mm.yyyy hh24:mi:ss') + 1/86400 * 1137452400 from dual;

The database is running on the same machine and the output of sqlplus> select sysdate from dual;
correspons to the output of
$ date'

at the shell.

Only in the seconds conversion thing there is one hour missing. Of course that has something to do with timezone settings. But I cant figure out what is right or wrong. Please help searching this one hour.

The AIX Environment concerning locale is like

---

LANG=de_DE.ISO8859-1
NLS_LANG=German_Germany.WE8ISO8859P1
TZ=NFT-1DFT,M3.5.0/02:00:00,M10.5.0/03:00:00 The date specific Oracle 8.1.7 session environment is like

---

NLS_LANGUAGE	        GERMAN
NLS_TERRITORY	        GERMANY
NLS_CALENDAR	        GREGORIAN
NLS_DATE_FORMAT	        DD.MM.RR
NLS_DATE_LANGUAGE	GERMAN
NLS_TIME_FORMAT	        HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT	DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT	HH24:MI:SSXFF TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT	DD.MM.RR HH24:MI:SSXFF TZH:TZM

Any advice is highly welcome and appreciated. Thanks! Carsten Received on Wed Jan 25 2006 - 06:00:53 CST