SQL- Difference between TIMESTAMP, DATE AND TIMESTAMP WITH TIMEZONE?

Question

What is the difference between TIMESTAMP , DATE AND TIMESTAMP with TIMEZONE?

E.g if I wanted to search for all entries between 01-JAN-1990 and 01-JAN-2000 , how would I do so in each format?

I have been searching for timestamp as:

SELECT COUNT(*) FROM TABLE_NAME WHERE DATE BETWEEN '01-JAN-1990' AND '01-JAN-2000;

But I am not sure what format to use to search for DATE or TIMESTAMP WITH TIMEZONE.

score 9 · Accepted Answer · edited Jul 18 '21 at 10:03

The data types and differences between them are in the documentation. The short version is:

DATE has precision down to a second with no time zone support;
TIMESTAMP has precision down to fractions of a second (up to nine decimal places, but your operating system affects that too), still with no time zone support;
TIMESTAMP WITH TIME ZONE has the same precision as TIMESTAMP but also has time zone support, as the name suggests;
TIMESTAMP WITH LOCAL TIME ZONE adjusts the stored value to and from the creating/querying session's local time zone.

You might find this article interesting too.

Whenever you are comparing datetime values stored in your database you should use values of the same datatype to compare against. You don't want to have to convert every value in the column for comparison, especially if the column is indexed. If you have a DATE column then compare with a DATE - don't compare as a string, and don't rely on implicit conversion of a string. When you do:

WHERE date_col BETWEEN '01-JAN-1990' AND '01-JAN-2000'

you are relying on your NLS_DATE_FORMAT being DD-MON-YYYY and your NLS_DATE_LANGUAGE being English. If someone else runs the same query in another session their settings may cause the query to fail (or in some cases, give wrong results, which can be worse). To avoid the language issue it's better to use month numbers rather than names. If you have a string variable to compare against you should use TO_DATE() to convert the string to a DATE using a fixed known format mask - don't rely on NLS. If you have a fixed value you can do the same, or you can use a date literal, which is shorter and unambiguous.

With the format you used you are also including any rows which have a the column set to midnight on January 1st 2000, but not any later on that day. That may be what you want, but make sure you understand how BETWEEN works. If you're actually looking for dates within that decade, including at any time on December 31st 1999, you can use:

WHERE date_col >= DATE '1990-01-01' AND date_col < DATE '2000-01-01'

For timestamps you can either use TO_TIMESTAMP() or a timestamp literal:

WHERE ts_col >= TIMESTAMP '1990-01-01 00:00:00'
AND ts_col < TIMESTAMP '2000-01-01 00:00:00'

For timestamps with time zones you can either use TO_TIMESTAMP_TZ() or a timestamp literal, with a names time zone region:

WHERE tstz_col >= TIMESTAMP '1990-01-01 00:00:00 America/New_York'
AND tstz_col < TIMESTAMP '2000-01-01 00:00:00 America/New_York'

score 3 · Answer 2 · answered Jan 15 '16 at 16:24

Don't compare dates with strings. It can work if your session's nls_date_format happens to match the format of the string that you're using. But then your query will immediately fail for someone who has a different configuration. Compare dates with dates, timestamps with timestamps, etc.

For dates, you can use either ANSI date literals

SELECT COUNT(*)
  FROM your_table
 WHERE date_column BETWEEN date '1900-01-01' AND date '2000-01-01'

or you can use a to_date with an explicit format mask

SELECT COUNT(*)
  FROM your_table
 WHERE date_column BETWEEN to_date('1900-01-01', 'YYYY-MM-DD') 
                       AND to_date('2000-01-01', 'YYYY-MM-DD')

Note that a date in Oracle always has a day and a time component. If you don't specify a time in your to_date, it will default to midnight. If you use an explicit to_date, you can use a string in any format just so long as it matches the format mask you pass in as the second parameter.

For timestamps, you can either use an ANSI timestamp literal

SELECT COUNT(*)
  FROM your_table
 WHERE timestamp_column BETWEEN timestamp '1900-01-01 00:00:00.000' 
                            AND timestamp '2000-01-01 00:00:00.000'

or you can use a to_timestamp with an explicit format mask

SELECT COUNT(*)
  FROM your_table
 WHERE timestamp_column BETWEEN to_timestamp('1900-01-01 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.FFF') 
                            AND to_timestamp('2000-01-01 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.FFF')

If you use an explicit to_timestamp, you can use a string in any format just so long as it matches the format mask you pass in as the second parameter.

For timestamps with time zone, as you may have guessed, you can either use an ANSI timestamp literal

SELECT COUNT(*)
  FROM your_table
 WHERE timestamp_column BETWEEN timestamp '1900-01-01 00:00:00.000 -05:00' 
                            AND timestamp '2000-01-01 00:00:00.000 -05:00'

or you can use the to_timestamp_tz function with an explicit format mask

SELECT COUNT(*)
  FROM your_table
 WHERE timestamp_column BETWEEN to_timestamp('1900-01-01 00:00:00.000 -05:00', 'YYYY-MM-DD HH24:MI:SS.FFF TZH:TZM') 
                            AND to_timestamp('2000-01-01 00:00:00.000 -05:00', 'YYYY-MM-DD HH24:MI:SS.FFF TZH:TZM')

If you use an explicit to_timestamp_tz, you can use a string in any format just so long as it matches the format mask you pass in as the second parameter.

SQL- Difference between TIMESTAMP, DATE AND TIMESTAMP WITH TIMEZONE?

2 Answers2

Linked