- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Problem with a time field in an Oracle 8i database
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-17-2002 05:51 AM
тАО10-17-2002 05:51 AM
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-17-2002 05:53 AM
тАО10-17-2002 05:53 AM
Re: Problem with a time field in an Oracle 8i database
Try this:-
date
and
date -u
Do they differ by 2 hours?
Paula
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-17-2002 05:55 AM
тАО10-17-2002 05:55 AM
Re: Problem with a time field in an Oracle 8i database
Yes: 'date -u' is 2 hours earlier than 'date'
John
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-17-2002 06:58 AM
тАО10-17-2002 06:58 AM
Re: Problem with a time field in an Oracle 8i database
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-20-2002 11:08 PM
тАО10-20-2002 11:08 PM
Re: Problem with a time field in an Oracle 8i database
John
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-20-2002 11:31 PM
тАО10-20-2002 11:31 PM
SolutionYes, 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-20-2002 11:42 PM
тАО10-20-2002 11:42 PM
Re: Problem with a time field in an Oracle 8i database
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-20-2002 11:45 PM
тАО10-20-2002 11:45 PM
Re: Problem with a time field in an Oracle 8i database
Also here:-
http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x5c607bb04b5cd611abdb0090277a778c,00.html
Note Deepak's last answer.
Paula
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-20-2002 11:50 PM
тАО10-20-2002 11:50 PM
Re: Problem with a time field in an Oracle 8i database
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-30-2002 02:29 AM
тАО10-30-2002 02:29 AM