I need to look up rows within a particular time frame.
select * from TableA where startdate >= '12-01-2012 21:24:00' and startdate <= '12-01-2012 21:25:33' I.e.: I need to look up rows with timestamp precision of SECONDS. How do I achieve this?
FYI: The startdate column is of type TIMESTAMP.
2 Answers
to_timestamp()
You need to use to_timestamp() to convert your string to a proper timestamp value:
to_timestamp('12-01-2012 21:24:00', 'dd-mm-yyyy hh24:mi:ss') to_date()
If your column is of type DATE (which also supports seconds), you need to use to_date()
to_date('12-01-2012 21:24:00', 'dd-mm-yyyy hh24:mi:ss') Example
To get this into a where condition use the following:
select * from TableA where startdate >= to_timestamp('12-01-2012 21:24:00', 'dd-mm-yyyy hh24:mi:ss') and startdate <= to_timestamp('12-01-2012 21:25:33', 'dd-mm-yyyy hh24:mi:ss') Note
You never need to use to_timestamp() on a column that is of type timestamp.
For everyone coming to this thread with fractional seconds in your timestamp use:
to_timestamp('2018-11-03 12:35:20.419000', 'YYYY-MM-DD HH24:MI:SS.FF')
