compare 13digit (millisecond) unix timestamp with date in oracle

Question

A database column (VARCHAR2 datatype) stores the date/time as 13 digit (milliseconds ) unixtimestamp format. Now when I want to compare the column with a oracle date (in question), The error thrown as 'invalid number'

I tried both ways,

converting the 13digit number to Date and compare with the date in question like below. The expressions seems valid as they are printed in select query, but if i include in the where part, it throws 'invalid number'

Here 'value' is 13th digit unixtimestamp column of VARCHAR2 datatype.

select
TO_DATE('1970-01-01', 'YYYY-MM-DD') + value/86400000,
TO_DATE('2014-04-21', 'YYYY-MM-DD')
from dummytable 

-- where and TO_DATE('1970-01-01', 'YYYY-MM-DD') + value/86400000 > TO_DATE('2014-04-21', 'YYYY-MM-DD')

converting the date in question to 13digit unixtimestamp and comparing with the database column.The expressions seems valid as they are printed in select query, but if i include in the where part, it throws 'invalid number'

.

select
value,
(to_date('2013-04-21', 'YYYY-MM-DD') - to_date('1970-01-01', 'YYYY-MM-DD')) * (1000*24*60*60)
from dummytable 

-- where value > ((to_date('2013-04-21', 'YYYY-MM-DD') - to_date('1970-01-01', 'YYYY-MM-DD')) * (1000*24*60*60))

any pointers? Thanks in advance.

[EDIT- 1 day later] I see the problem now. There are some data (other rows) for the 'value' column that are non-numeric. But I have another column say field, where always field='date' return value as 13 digit timestamp. Now I think when 'where' condition executes, although the field='date' is in the condition, it is still validating the other values for 'value' which are non-numeric. Is there a way to avoid this ?

I found that it is view and not a table. But does it make any difference. — ulab, May 15 '14 at 08:42
Resolving type conversion errors in the `where` clause is covered in the answers to this question: http://stackoverflow.com/questions/3105003/literal-does-not-match-format-string-for-oracle-sql-to-date-on-a-string-column/3105218#3105218 — Allan, May 15 '14 at 15:53

AndreySarafanov · Accepted Answer · 2014-05-16T13:06:26.903

Your code works just fine. The problem is in your data. Some of your values is not a number.

create table test
(value varchar2(13));

insert into test(value) values('2154534689000');
--insert into test(value) values('2 54534689000');

select TO_DATE('1970-01-01', 'YYYY-MM-DD') + value/86400000
from test
where TO_DATE('1970-01-01', 'YYYY-MM-DD') + value/86400000 > TO_DATE('2014-04-21', 'YYYY-MM-DD');

This code works fine. But if you uncommented the second insert, you would get exactly the same invalid number error as you get.

UPD. Allan gave you a nice hint, but i feel that it can be good to explain you a bit about views. The fact that you select from a view CAN make a difference. A view is not stored somewhere physically, when you select from it, it is just "added to your query". And then Oracle Query Optimizer starts working. Among other things, it can change the order in which your where predicates are evaluated. For example, your the view query can have a line where value is not null and it would normally show only 'good' values. But if your query has a predicate where to_date(value,'ddmmyyyy') > sysdate, Oracle can decide to evaluate your predicate earlier, because Oracle predicts that it would "cut off" more rows, thus making the whole query faster and less momery consuming. Of course, execution will crash because of an attempt to convert a null string to date. I believe, that Allan in his answer that he gave a link to, gave a great way to solve this problem: "wrapping" your query in a subquery that Oracle can't "unwrap":

select value
from
  (select value
  from my_view
  where rownum > 0)
where to_date(value,'ddmmyyyy') > sysdate

Hope that helps.

To find the non-numeric character, try `select * from dummytable where LENGTH (TRIM (TRANSLATE (value, '1234567890', ' '))) is not null`. As long as the offending character isn't a space, the problem row should be returned. — Allan, May 14 '14 at 15:39
@Allan 'select value, LENGTH(value) from dummytable'; returns '13' only. which means there is no space. What could be the offending character. — ulab, May 15 '14 at 07:22
@Andrey : yes i tried with a new table and it all works fine. But some problem with definition of this column. Thanks.sorry I cant upvote with less reputation. — ulab, May 15 '14 at 08:42
Please see my edit to the post. It looks like data which is excluded from where condition creates the problem — ulab, May 15 '14 at 12:11

compare 13digit (millisecond) unix timestamp with date in oracle

1 Answers1