Oracle SQL:Timestamps in Where Clause

Oracle SQL : timestamps in where clause

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.

Search data using Timestamp datatype in Oracle Where clause

You are trying to compare timestamp with date. Timestamp also have milliseconds. Assuming TIMESTAMP is of type timestamp likely this will work

Select * from ALERT_LOGS WHERE KPI_DEF_ID = 1000571 and to_char(TIMESTAMP, 'DD.MM.YY HH24:MI:SS') = '17.10.15 00:02:58';  

This is how timestamp is represented.

SQL> select to_timestamp('02.12.15 08:40:54', 'DD.MM.YY HH24:MI:SS') from dual;

TO_TIMESTAMP('02.12.1508:40:54','DD.MM.YYHH24:MI:SS')

02-DEC-15 08.40.54.000000000 AM

filter timestamp column in SQL Oracle

Use a TIMESTAMP literal:

SELECT *
FROM   table_name
WHERE  run_time >= TIMESTAMP '2020-08-27 00:00:00';

or, use a DATE literal:

SELECT *
FROM   table_name
WHERE  run_time >= DATE '2020-08-27';

or, use TO_TIMESTAMP with a format model:

SELECT *
FROM   table_name
WHERE  run_time >= TO_TIMESTAMP( '2020-08-27', 'YYYY-MM-DD' );

or, use TO_DATE with a format model:

SELECT *
FROM   table_name
WHERE  run_time >= TO_DATE( '2020-08-27', 'YYYY-MM-DD' );

Which for the sample data:

CREATE TABLE TABLE_NAME ( run_time TIMESTAMP(6) );

INSERT INTO table_name ( run_time )
SELECT TIMESTAMP '2020-07-22 04:22:07' FROM DUAL UNION ALL
SELECT TIMESTAMP '2020-07-22 04:34:07' FROM DUAL UNION ALL
SELECT TIMESTAMP '2020-07-22 04:45:07' FROM DUAL UNION ALL
SELECT TIMESTAMP '2020-07-22 04:50:07' FROM DUAL UNION ALL
SELECT TIMESTAMP '2020-07-22 04:55:08' FROM DUAL UNION ALL
SELECT TIMESTAMP '2020-08-26 23:59:59.999999' FROM DUAL UNION ALL
SELECT TIMESTAMP '2020-08-27 00:00:00' FROM DUAL;

All output:

| RUN_TIME                  |
| :------------------------ |
| 27-AUG-20 00.00.00.000000 |

db<>fiddle here

Oracle SQL where clause against a timestamp column

Learn about prepared statements, and use native types instead of strings:

java.sql.Date extDate = new java.sql.Date(getDateTemp.getTime());
String sql = "SELECT * from registration_timestamp where timestamp_registered=?";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setDate(1, extDate);
ResultSet rs = stmt.executeQuery();

That said, since you're talking about timestamps, and timestamps have a precision that goes to the nanosecond, the problem could be that the date stored in the database has a time part in addition to a date part:

16/02/2013 != 16/02/2013 18:54:32 123421

If that's the case (and it seems it is), compare the chosen date with the timestamp truncated to a date:

select * from registration_timestamp where trunc(timestamp_registered) = ?

See http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions201.htm#i79761

How do I get this timestamp in the format I want, Oracle SQL

As @Gordon said, timestamps (and dates) are not stored in a format you would recognise Oracle uses an internal representation that you never really need to know about or examine (but it is documented if you're interested in that sort of thing).

When you query a timestamp it is displayed using your client's NLS settings, unless you have a client that overrides those. I can set my session up to match what you are seeing:

alter session set nls_timestamp_format = 'DD-MON-RR HH.MI.SS.FF AM';

select to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS') from dual;

TO_CHAR(SYSTIMESTAM
-------------------
2018-07-10 15:37:31

select to_timestamp(to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') from dual;

TO_TIMESTAMP(TO_CHAR(SYSTIMESTA
-------------------------------
10-JUL-18 03.37.31.000000000 PM

And I can change it see what you want to see:

alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS';

select to_timestamp(to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') from dual;

TO_TIMESTAMP(TO_CHA
-------------------
2018-07-10 15:37:32

But all you are doing is converting from a timestamp with time zone (which is what systimestamp is) to a string and then back to a timestamp. You are losing the time zone portion, and any fractional seconds; which you could also do with a cast:

select cast(systimestamp as timestamp(0)) from dual;

CAST(SYSTIMESTAMPAS
-------------------
2018-07-10 15:37:32

You can see the timezone and fraction seconds with your default timestamp_tz format:

select systimestamp from dual;

SYSTIMESTAMP                        
------------------------------------
2018-07-10 15:37:33.776469000 +01:00

and change it with a different alter:

alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS.FF3 TZH:TZM';

select systimestamp from dual;

SYSTIMESTAMP                  
------------------------------
2018-07-10 15:37:34.070 +01:00

Which isn't entirely relevant if you're really talking about storing timestamps in a table, but shows that there are variations.

In your table make the data type timestamp (or timestamp with time zone or timestamp with local time zone), and only worry about formatting the value as a string for presentation to the end user, at the last possible moment.

When you do need to display it, if the display format is important to you then use to_char() with an explicit format mask - do not assume that anyone else running your queries will have the same NLS settings. As you can see, it's easy to change those to modify the output. (Most clients have a way to let you set the defaults so you don't have to do the same alter commands every time you connect; e.g. in SQL Developer, from Tools->Preferences->Database->NLS). If you want to always show the same format then use something like:

select to_char(your_column, 'YYYY-MM-DD HH24:MI:SS') as column_alias
from your_table
where your_column < timestamp '2018-01-01 00:00:00'

which also shows the column value being filtered (as a timestamp still) using a timestamp literal.

Comparing date and timestamp with BETWEEN clause

TO_TIMESTAMP and TO_DATE both take a string and convert it to a timestamp/date respectively using the specified format mask.

Therefore doing TO_TIMESTAMP(TO_DATE(..)...) doesn't make sense - especially when your original column is already in a date format! You need one or the other.

By doing TO_TIMESTAMP(TO_DATE(<date_column>..)...), what you're doing is forcing oracle to do some implicit conversions from a date into a string and back to a date then back to a string, so that it can be converted back into a timestamp. That looks something like this:

TO_TIMESTAMP(TO_CHAR(TO_DATE(TO_CHAR(<date_column>, <nls_date_format>), <your_date_format>), <nls_date_format>), <your_timestamp_format>).

That's massively overcomplicated, right? Plus, if one of those formats doesn't match, then you'll get the errors you're experiencing.

If you want to convert a DATE into a TIMESTAMP, you can use CAST, e.g.:

where systimestamp between cast(datea as timestamp) and cast(dateb as timestamp)

or you can let Oracle handle the implicit conversion from the date into a timestamp and just do:

where systimestamp between datea and dateb

or simply avoid the issue altogether by using sysdate instead of systimestamp, i.e.:

where sysdate between datea and dateb

Subtracting/Adding to a Where clause timestamp condition

You can add an interval to a timestamp; you can read about datetime and interval arithmetic. There are conversion functions to go from a variable to an interval, but with known fixed values you can use an interval literal here. You can use the full string you have:

where search_date >= causedat - interval '000000000 00:30:00.000000000' day to second
and   search_date <= causedat + interval '000000000 00:30:00.000000000' day to second;

Or just the non-zero part:

where search_date >= causedat - interval '30' minute
and   search_date <= causedat + interval '30' minute;

Trivial demo of how these evaluate:

select systimestamp,
  systimestamp - interval '000000000 00:30:00.000000000' day to second as minus_30,
  systimestamp + interval '000000000 00:30:00.000000000' day to second as plus_30
from dual;

SYSTIMESTAMP                        MINUS_30                            PLUS_30                           
----------------------------------- ----------------------------------- -----------------------------------
18-NOV-15 11.39.09.597473000 +00:00 18-NOV-15 11.09.09.597473000 +00:00 18-NOV-15 12.09.09.597473000 +00:00

select systimestamp,
  systimestamp - interval '30' minute as minus_30,
  systimestamp + interval '30' minute as plus_30
from dual;

SYSTIMESTAMP                        MINUS_30                            PLUS_30                           
----------------------------------- ----------------------------------- -----------------------------------
18-NOV-15 11.39.09.653809000 +00:00 18-NOV-15 11.09.09.653809000 +00:00 18-NOV-15 12.09.09.653809000 +00:00

Forming where clause on Timestamp column in Oracle, The data is in java.util.Date format

If you absolutely can't use prepared statements, this should work. Format the date as you will, and then use TO_DATE in your sql statment using the same format. Oracle has a default format, but I prefer explicitly stating the format.

Date now = new Date();
String dateString = new SimpleDateFormat("yyyy-MM-dd").format(now);
String sqlVal = "TO_DATE('" + dateString + "', 'yyyyy-mm-dd')";
String sql = "SELECT * FROM DOWNLOAD_TAB WHERE download_date > " + sqlValue;

And that's why a prepared statement is always preferred. As you can see, this code is ugly, error-prone, non-portable across databases, and open to sql injection attacks. And of course, prepared statement perform better.

---

Related Topics