CODEWITHSUNDEEP
sqloracle-databasetimestamp
Kevin
Kevin

Reputation: 685

Compare timestamps in Oracle

I have a column which is used to store date and time data in a VARCHAR2 column in my DB in Oracle 11g Express in the format of:

9/30/2016 14:00:00

I was trying out ways to get data between time ranges. I found the following 2 ways:

select * 
from dummy
WHERE starttime > '9/30/2016 14:00:00' 
AND starttime < '9/30/2016 17:00:00' 
order by starttime;

select * 
from dummy 
WHERE to_timestamp(starttime, 'mm/dd/yyyy hh24:mi:ss') > TO_TIMESTAMP('9/30/2016 14:00:00', 'mm/dd/yyyy hh24:mi:ss') 
AND TO_TIMESTAMP(starttime, 'mm/dd/yyyy hh24:mi:ss') < TO_TIMESTAMP('9/30/2016 17:00:00', 'mm/dd/yyyy hh24:mi:ss');

I was wondering how the first method works too as the column starttime is stored in VARCHAR format and without converting to a Timestamp the comparison still works. Could someone explain to me how/ why that happens or if there is some corner case for which it will not work? Thanks.

Upvotes: 3

Views: 22607

Answers (5)

Sham Fiorin
Sham Fiorin

Reputation: 539

On Oracle, you could use too:

SELECT * FROM table 
WHERE field BETWEEN TRUNC(SYSDATE - 6) AND SYSDATE

Upvotes: 0

MT0
MT0

Reputation: 168681

Storing the values in a VARCHAR column means that you will do a string comparison:

SELECT * 
FROM   dummy
WHERE  starttime > '9/30/2016 14:00:00' 
AND    starttime < '10/30/2016 17:00:00' 
ORDER BY starttime;

This would look at the start time and consider it character-by-character and if the 1st character is greater than '9' and also less than '1' then it will return a row (since this will never be true it will not return a row). Moreover, it will not consider that the 9 and the 10 represent the months and that when doing a string comparison '09/30/2016' < '09/31/1900' < '10/30/2016'.

Even if you store the value in a TIMESTAMP column, using string literals is a bad idea:

SELECT * 
FROM   dummy
WHERE  starttime > '9/30/2016 14:00:00' 
AND    starttime < '9/30/2016 17:00:00' 
ORDER BY starttime;

This works as Oracle will perform an implicit cast (using TO_TIMESTAMP( time, format_mask )) using the session parameter NLS_TIMESTAMP_FORMAT as the format mask.

So your query would (assuming a TIMESTAMP data type) effectively be (although Oracle will implement it in a more efficient fashion):

SELECT * 
FROM   dummy
       CROSS JOIN
       ( SELECT value AS format_mask
         FROM NLS_SESSION_PARAMETERS
         WHERE PARAMETER = 'NLS_TIMESTAMP_FORMAT' ) nls
WHERE  starttime > TO_TIMESTAMP( '9/30/2016 14:00:00', nls.format_mask )
AND    starttime < TO_TIMESTAMP( '9/30/2016 17:00:00', nls.format_mask )
ORDER BY starttime;

The NLS_TIMESTAMP_FORMAT is a session parameter - this means that each user can set their own value for this parameter in their own session and if one user changes it to YYYY-MM-DD"T"HH24:MI:SS.ZZZ"Z" (i.e. an ISO8601 format) then your query will break for that user (and not for the other users who have not changed it) without any changes having been made to your query.

Rather than using a string literal and implicit conversion, it is better to either explicitly set the format mask you are expecting or to use an ANSI TIMESTAMP literal:

SELECT * 
FROM   dummy
WHERE  TO_TIMESTAMP( starttime, 'MM-DD-YYYY HH24:MI:SS' ) > TIMESTAMP '2016-09-30 14:00:00' 
AND    TO_TIMESTAMP( starttime, 'MM-DD-YYYY HH24:MI:SS' ) < TIMESTAMP '2016-09-30 17:00:00'
ORDER BY TO_TIMESTAMP( starttime, 'MM-DD-YYYY HH24:MI:SS' );

You would then benefit from a function-based index on TO_TIMESTAMP( starttime, 'MM-DD-YYYY HH24:MI:SS' ).

Even better, would be to convert your column to the correct TIMESTAMP format then you do not need a function-based index and can just use TIMESTAMP literals for the bounds without any need for conversion functions.

Upvotes: 2

BobC
BobC

Reputation: 4432

It's a bad idea to store date/times as character strings or numbers. The optimizer has no idea of the domain and so when attempting to estimate the cardinality you are not giving the optimizer the best chance. For example, consider the following two dates

Dec 31st 2016
Jan 1st 2017

If you store these as a number, you might use

20170101 and 20161231

So what is the number of days between them? Using numbers, you get

20170101 - 20161231
= 8870

However, the true (date based) answer is one.

Although you can TO_DATE() or CAST your columns, you now run the risk of not being able to use certain optimizations, such as indexing, partition pruning, bloom filtering etc.

So I highly recommend using the correct data types.

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

This works because you have no issue with months and years or one-digit vs. two-digit days. Think of any strings that are inside the range '9/30/2016 14:00:00' to '9/30/2016 17:00:00'. They will all have to start with '9/30/2016 1'.

If the range where, say '9/30/2016 14:00:00' to '10/30/2016 17:00:00', you wouldn't find any record at all, because the string would have to start with somthing >= '9' and <= '1' which is not possible.

So it is the narrow range within a particular day that saved you here :-)

Upvotes: 3

JohnHC
JohnHC

Reputation: 11205

Storing date as varchar is less than clever...

Your first method is fine, provided you don't need to cross the boundary of a year. The numbers are compared left to right (because text). Unless you store as 'YYYY-MM-DD HH24:MI:SS' you will run into problems.

2 options, change that storage to DATE, or use a to_date or to_timestamp on the WHERE clause (I recommend to_date)

Upvotes: 1

Related Questions