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.
Advertisement
Answer
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'