Title: RE: Date format is unreadable
It works better but it still does give correct output. I'd like to see hour,min,sec as well.
1 select user_name,node_name,to_date('01/01/1970', 'MM/DD/') + time_stamp / 86400 from user_activity_log
2 from user_activity_log
3 where user_name
Title: RE: Date format is unreadable
to_date('01/01/1970', 'MM/DD/') + time_stamp / 86400
is a DATE expression
formatting of DATE values is done with the TO_CHAR function (see SQL Reference Manual)
for example:
to_char (to_date('01/01/1970', 'MM/DD/') + time_stamp / 86400,
'/MM
David, try setting numwidth in sqlplus...
(for example)
1* create table dtest (col1 varchar2(20), col2 number(20))
SQL /
Table created.
SQL insert into dtest values('888','');
1 row created.
SQL select * from dtest;
COL1 COL2
David - This is not a date column. It is some type of number column (see
type number(20) ). You need to find the program that writes to this column
to find what the interpretation is. You may also want to
column time_stamp format
Dennis Williams
DBA, 40%OCP, 100% DBA
David,
I presume that your date is a Unix timestamp.
Try to see whether
to_date('01/01/1970', 'MM/DD/') + TIME_STAMP
yields something more meaningful to you ...
--
Regards,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
Title: RE: Date format is unreadable
David,
It's not a date column. Rather it's a number column that's reading as scientific notation. Some databases, like Access, actually store dates as numbers; however, AFAIK Oracle does not.
Those are some large numbers. 1.0397E+12 = 10397
Yes, it is a Unix timestamp. I use your syntax and I got following error.
1 select
user_name,client_ip,node_name,to_date('01/01/1970','MM/DD/')+tig
2 where user_name = 'admin'
3* order by time_stamp
SQL /
select user_name,client_ip,node_name,to_date('01/01/1970','MM/DD/') +
Ooops. Should have written
to_date('01/01/1970', 'MM/DD/') + TIME_STAMP / 86400
The timestamp is in seconds since 1/1/1970, while Oracle date arithmetic
is in days.
Sorry.
Nguyen, David M wrote:
Yes, it is a Unix timestamp. I use your syntax and I got following error.
1 select
