Home » SQL & PL/SQL » SQL & PL/SQL » SELECT WITH TIMESTAMP AND SYSDATE (ORACLE 12)
SELECT WITH TIMESTAMP AND SYSDATE [message #669792] Sun, 13 May 2018 12:19 Go to next message
cruizji
Messages: 1
Registered: May 2018
Junior Member
Hello, I am Junior in Oracle and I have a big problem. I need to write a Query to get information with sysdate but my date is in format timestamp(1510614000) but the date_type is number so I don't know how to write it.
I have an example:

COLUMN_NAME DATA_TYPE
----------- -----------
ID_CLIENT NUMBER
NAME VARCHAR
DATE NUMBER

1510614000=14/11/2017 0:00:00

I woulk like to write something like that:

SELECT * FROM x.table WHERE DATE > sysdate - 7 days

Could you help me, please?

Thank you.
Regards
Re: SELECT WITH TIMESTAMP AND SYSDATE [message #669794 is a reply to message #669792] Sun, 13 May 2018 13:51 Go to previous messageGo to next message
BlackSwan
Messages: 26004
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

  1* select to_char(to_date('1970-01-01','YYYY-MM-DD')+ 1510614000/60/60/24,'yyyy-mm-dd hh24:mi') from dual
SQL> /

TO_CHAR(TO_DATE(
----------------
2017-11-13 23:00

Re: SELECT WITH TIMESTAMP AND SYSDATE [message #669799 is a reply to message #669792] Mon, 14 May 2018 07:56 Go to previous messageGo to next message
EdStevens
Messages: 933
Registered: September 2013
Senior Member
cruizji wrote on Sun, 13 May 2018 12:19
Hello, I am Junior in Oracle and I have a big problem. I need to write a Query to get information with sysdate but my date is in format timestamp(1510614000) but the date_type is number so I don't know how to write it.
I have an example:

COLUMN_NAME DATA_TYPE
----------- -----------
ID_CLIENT NUMBER
NAME VARCHAR
DATE NUMBER

1510614000=14/11/2017 0:00:00

I woulk like to write something like that:

SELECT * FROM x.table WHERE DATE > sysdate - 7 days

Could you help me, please?

Thank you.
Regards
"DATE" is a reserved word in Oracle and so should not be used as an object name.

The fact that you (someone) created a "date" column but made it's data type other than DATE is a very, very serious design flaw. I understand you may not be able to (easily) change it now, but file this away for future reference. Using anything but DATE or TIMESTAMP for dates is a huge design fail and anyone on your team who suggests otherwise is simply unqualified to be doing design.
Re: SELECT WITH TIMESTAMP AND SYSDATE [message #669800 is a reply to message #669799] Mon, 14 May 2018 08:10 Go to previous message
Bill B
Messages: 1734
Registered: December 2004
Senior Member
To expand on what Ed said, storing the unix timestamp in your date column is not a great idea since you can easily convert it into an oracle timestamp when you insert into the database. Having it as an oracle date will allow much better searching and date manipulation.
Previous Topic: Bulk collect for inserting million of records
Next Topic: Query to find common column in more then two tables
Goto Forum:
  


Current Time: Sat May 26 14:55:23 CDT 2018