| Author |
Message |
ameyas7@yahoo.com
*nix forums beginner
Joined: 04 Feb 2005
Posts: 3
|
 Posted: Fri Feb 04, 2005 4:20 pm Post subject:
Re: converting timestamp in milliseconds to date or vice-versa in oracle
|
|
|
Hi Rauf,
i got it ... just in case if you are interested.
select TO_DATE('1970-01-01', 'YYYY-MM-DD')+
max(numtodsinterval(to_number(timestamp_col)/1000,'SECOND')) from
your_table;
where timestamp_col is the column name which stores the timestamp and
your_table is the required table.
| Quote: | On a side note... why use currentTimeMillis at all and store in
varchar2 field?... why not use sysdate and store it properly in the
Date column?
|
the timestamp is used for auditing purpose.
it stores the timestamp precisely, which is required at some of the
places in the system and the system is already in place so it would not
be advisable to change the type now 
cheers
amey |
|
| Back to top |
|
 |
Rauf Sarwar
*nix forums Guru
Joined: 03 May 2005
Posts: 353
|
| Posted: Fri Feb 04, 2005 1:49 pm Post subject:
Re: converting timestamp in milliseconds to date or vice-versa in oracle
|
|
|
ameyas7@yahoo.com wrote:
| Quote: | hey folks,
we have a varchar2 col which stores the timestamps (records r added
frm
java app which logs the currentTimeInMillis on that System)
now i want to write a procedure which will delete records older than
say 45 days
i can get the current date using sysdate but this is in DATE format
whereas my column is in varchar2 (also the value is in millis)
how do i convert from millis to date or vice-versa
regards
amey
|
System.currentTimeMillis() as described in javadocs "the difference,
measured in milliseconds, between the current time and midnight,
January 1, 1970 UTC"... so good luck doing the calculation in PL/SQL.
On a side note... why use currentTimeMillis at all and store in
varchar2 field?... why not use sysdate and store it properly in the
Date column?
Regards
/Rauf |
|
| Back to top |
|
|
Jim Kennedy
*nix forums Guru
Joined: 30 Apr 2005
Posts: 367
|
| Posted: Fri Feb 04, 2005 1:46 pm Post subject:
Re: converting timestamp in milliseconds to date or vice-versa in oracle
|
|
|
<ameyas7@yahoo.com> wrote in message
news:1107525884.144586.315700@z14g2000cwz.googlegroups.com...
| Quote: | hey folks,
we have a varchar2 col which stores the timestamps (records r added frm
java app which logs the currentTimeInMillis on that System)
now i want to write a procedure which will delete records older than
say 45 days
i can get the current date using sysdate but this is in DATE format
whereas my column is in varchar2 (also the value is in millis)
how do i convert from millis to date or vice-versa
regards
amey
So you store numbers in a varchar field. That makes sense. How would you |
do it by hand? Date can be converted to a variety of formats, look at the
doco. (sql reference book).
Jim |
|
| Back to top |
|
|
ameyas7@yahoo.com
*nix forums beginner
Joined: 04 Feb 2005
Posts: 3
|
| Posted: Fri Feb 04, 2005 1:04 pm Post subject:
converting timestamp in milliseconds to date or vice-versa in oracle
|
|
|
hey folks,
we have a varchar2 col which stores the timestamps (records r added frm
java app which logs the currentTimeInMillis on that System)
now i want to write a procedure which will delete records older than
say 45 days
i can get the current date using sysdate but this is in DATE format
whereas my column is in varchar2 (also the value is in millis)
how do i convert from millis to date or vice-versa
regards
amey |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
Oracle
