SQL- TIMESTAMP，DATE和TIMESTAMP与TIMEZONE之间的区别? [英] SQL- Difference between TIMESTAMP, DATE AND TIMESTAMP WITH TIMEZONE?

问题描述

TIMESTAMP，DATE和TIMESTAMP with TIMEZONE有什么区别?

例如，如果我要搜索01-JAN-1990和01-JAN-2000之间的所有条目，我将如何以每种格式搜索?

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;

但是我不确定搜索DATE或TIMESTAMP WITH TIMEZONE使用哪种格式.

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

推荐答案

数据类型及其之间的区别是

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

• DATE的精度降至1秒，不支持时区；
• TIMESTAMP的精度低至几分之一秒(最多九个小数位，但您的操作系统也会对此造成影响)，仍然没有时区支持；
• 带时区的TIMESTAMP具有与TIMESTAMP相同的精度，但顾名思义，它还具有tome区域支持；
• 具有本地时区的时间戳记可在创建/查询会话的本地时区之间来回调整存储的值.

您可能会找到本文也很有趣.

You might find this article interesting too.

每当您比较存储在数据库中的日期时间值时，都应使用相同数据类型的值进行比较.您不需要转换列中的每个值以进行比较，尤其是在对索引建立索引的情况下.如果您有DATE列，则与DATE进行比较-不要比较为字符串，也不要依赖隐式

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'

您所依赖的NLS_DATE_FORMAT是DD-MON-YYYY，而您的NLS_DATE_LANGUAGE是英语.如果其他人在另一个会话中运行相同的查询，则其设置可能导致查询失败(或在某些情况下，给出错误的结果，这可能更糟).为了避免语言问题，最好使用月份数字而不是名称.如果您有要比较的字符串变量，则应使用 TO_DATE() 使用固定的已知格式掩码将字符串转换为DATE －不要依赖NLS.如果您有固定值，则可以执行相同操作，也可以使用日期字面量，它简短而明确.

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.

使用您使用的格式，您还将包括所有列的列设置为2000年1月1日午夜，但当天晚些时候不包含的行.那可能就是您想要的，但是请确保您了解BETWEEN的工作方式.如果您实际上是在寻找该十年内的日期，包括1999年12月31日的任何时间，则可以使用:

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'

对于时间戳，您可以使用 TO_TIMESTAMP() 或时间戳文字:

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'

对于带有时区的时间戳，您可以使用 TO_TIMESTAMP_TZ() 或带有时区名称的时间戳文字:

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'

这篇关于SQL- TIMESTAMP，DATE和TIMESTAMP与TIMEZONE之间的区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！