unix time insertion into db? [message #421881] |
Fri, 11 September 2009 05:56  |
alnhk
Messages: 1 Registered: September 2009
|
Junior Member |
|
|
using oracle to_timestamp, can we convert this unix time - "Sep 4, 2009 1:31:06 PM PDT" to "2009-10-04 13:31:06"???
I need to capture time difference of the mentioned time stamp - between "Sep 4, 2009 1:31:06 PM PDT" and latest time stamp.
|
|
|
Re: unix time insertion into db? [message #421882 is a reply to message #421881] |
Fri, 11 September 2009 06:10   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I can't get 2009-10-04, but I can get 2009-09-04 (as your original date is for september, not october).
SQL> select to_char(to_date('Sep 4, 2009 1:31:06 PM PDT','Mon dd, yyyy hh:mi:ss AM "PDT"')
2 ,'yyyy-mm-dd hh24:mi:ss') new_date
3 from dual;
NEW_DATE
-------------------
2009-09-04 13:31:06
|
|
|
Re: unix time insertion into db? [message #421883 is a reply to message #421881] |
Fri, 11 September 2009 06:12   |
soni_7
Messages: 33 Registered: July 2005
|
Member |
|
|
Hi,
If you are going to pass the unix date to the oracle, you need to pass it as a string.
Inside oracle try your to_timestamp using the value that is passed and giving required format. Should work.
Enjoy
Soni
|
|
|
Re: unix time insertion into db? [message #421991 is a reply to message #421881] |
Sat, 12 September 2009 18:32  |
wakula
Messages: 150 Registered: February 2008 Location: Poland
|
Senior Member |
|
|
Hi,
I believe that the question asked is not really clear.
It seems that you have some unix timestamp and want to insert it into the database. Oracle has several formats of timestamp (ex. timestamp which gives time in local timezone, timestamp which gives time in servers timezone, timestamp with...).
Thing to note: Oracle's timestamp has no format predefined! Since this is a datatype it has no format - however its literal presentation has.
You should ask yourself the following questions:
- What format of the timestamp do I have? Can I change the format? What is its source? What timezone id used for it?
- How the data are inserted into the Oracle? Am I using it as I should? Is the format always specified or am I incorrectly using NLS settings?
- What information is going to be used later: the timestamp or the string describing that time.
You should use it in the way similar to the following:
INSERT INTO x VALUES(TO_DATE('2009-12-31','YYYY-MM-DD')
Notes:
- Please refer to ISO-8601 to read about some bad habits about using different date/time formats.
- Physical database structure has sme timestamp format - but it should never be needed for you unless you are reading Oracle datafiles without Oracle itself
|
|
|