Home » SQL & PL/SQL » SQL & PL/SQL » unix time insertion into db? (Sun solaris 10 and Oracle 10g)
unix time insertion into db? [message #421881] Fri, 11 September 2009 05:56 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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 Wink
Previous Topic: sql equivalent of dbms_utility.comma_to_table ?
Next Topic: Improving sql sentence (merged)
Goto Forum:
  


Current Time: Sat Feb 08 20:43:57 CST 2025