Operating System - HP-UX
1748022 Members
4732 Online
108757 Solutions
New Discussion юеВ

Re: Problem with a time field in an Oracle 8i database

 
SOLVED
Go to solution
John Strang
Regular Advisor

Problem with a time field in an Oracle 8i database

Hi,

Problem is this:
column in a table holds "epoch time" ie seconds since 1/1/1970.

When I use the SQL command
TO_CHAR(TO_DATE(MOD(HD_TS_INTCREATION, 86400))), 'SSSSS'),'HH24:MI:SS') "Time"

(which I found when searching resource forum)
the value returned is wrong by 2 hours: it returns, eg, 08:00:00 instead of 10:00:00.

OS is HP-UX 11.00
$TZ is MET-1METDST
Oracle version is 8.1.7.4

Please can anyone advise on how to return the correct time?

Thanks in advance,

John
If you never make a mistake you'll never make anything.
9 REPLIES 9
Paula J Frazer-Campbell
Honored Contributor

Re: Problem with a time field in an Oracle 8i database

John

Try this:-

date

and

date -u

Do they differ by 2 hours?

Paula
If you can spell SysAdmin then you is one - anon
John Strang
Regular Advisor

Re: Problem with a time field in an Oracle 8i database

Paula,

Yes: 'date -u' is 2 hours earlier than 'date'

John


If you never make a mistake you'll never make anything.
Paula J Frazer-Campbell
Honored Contributor

Re: Problem with a time field in an Oracle 8i database

John

I would imagine that date -u is the two hours earlier date.

You can correct in your sql command by adding 120 secinds to your epoc time string.

See man date


Paula
If you can spell SysAdmin then you is one - anon
John Strang
Regular Advisor

Re: Problem with a time field in an Oracle 8i database

Adding number of seconds is OK, but that would need to change when SummerTime ends as we will then be only 1 hour ahead of GMT - is there a way of using a variable in the SQL script to cater for this?

John
If you never make a mistake you'll never make anything.
John Palmer
Honored Contributor
Solution

Re: Problem with a time field in an Oracle 8i database

Hi,

Yes, you could pass an argument to your SQL script, something like the following works here in the UK (where date returns either GMT or BST...

In the shell script that calls the SQL
if date | grep -q BST
then EPOCH_OFFSET=3600
else EPOCH_OFFSET=0
fi

Yours would read:
if date | grep -q ???
then EPOCH_OFFSET=7200
else EPOCH_OFFSET=3600
fi

Then call SQLPLUS with the additional argument ${EPOCH_OFFSET} and amend your SQL to read:

TO_CHAR(TO_DATE(MOD(HD_TS_INTCREATION + &1, 86400))), 'SSSSS'),'HH24:MI:SS') "Time"

Regards,
John
Paula J Frazer-Campbell
Honored Contributor

Re: Problem with a time field in an Oracle 8i database

John

That should have been 7200 seconds and not 120.


This may help :-

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0xf2ac3a7b3682d611abdb0090277a778c,00.html


Also search the forum for epoch and you will find lots of info.

Be careful on your conversion to time in the past as you will have to take into consideration yout local TZ at tne date of the database entry.


Paula
If you can spell SysAdmin then you is one - anon
Paula J Frazer-Campbell
Honored Contributor

Re: Problem with a time field in an Oracle 8i database

John

Also here:-

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x5c607bb04b5cd611abdb0090277a778c,00.html


Note Deepak's last answer.

Paula
If you can spell SysAdmin then you is one - anon
John Strang
Regular Advisor

Re: Problem with a time field in an Oracle 8i database

Hello John & Paula,

I'm not able to work on this today, another emergency has taken priority:-(

I will reply to let you know how it all goes.

Thanks again,

John
If you never make a mistake you'll never make anything.
John Strang
Regular Advisor

Re: Problem with a time field in an Oracle 8i database

Thanks again for replies, I think the suggestion of using an environment variable will be best.
If you never make a mistake you'll never make anything.