Menu
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.
267
The TIMESTAMP datatype is an extension of the DATE datatype. It stores year, month, day, hour, minute, and second values. It also stores fractional seconds, which are not stored by the DATE datatype.
You can use the below code: insert into tablename (timestamp_value) values (TO_TIMESTAMP(:ts_val, 'YYYY-MM-DD HH24:MI:SS')); If you need the current timestamp to be inserted then use the following code: insert into tablename (timestamp_value) values (CURRENT_TIMESTAMP);
Introduction to Oracle TIMESTAMP data typeThe TIMESTAMP data type allows you to store date and time data including year, month, day, hour, minute and second. In addition, it stores the fractional seconds, which is not stored by the DATE data type.
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') 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') 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') You never need to use to_timestamp() on a column that is of type timestamp.
59
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
