PostgreSQL：在时间戳字段上选择类似的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4799615/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL: Select data with a like on timestamp field
提问by Kevin Campion
I am trying to select data from a table, using a "like" on date field "date_checked" (timestamp). But I have this error :
我正在尝试从表中选择数据，在日期字段“date_checked”（时间戳）上使用“like”。但我有这个错误：
SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: timestamp without time zone
My request is :
我的要求是：
SELECT my_table.id
FROM my_table
WHERE my_table.date_checker LIKE '2011-01-%'
I don't want to use :
我不想使用：
SELECT my_table.id
FROM my_table
WHERE my_table.date_checker >= '2011-01-01 00:00:00'
AND my_table.date_checker < '2011-02-01 00:00:00'
回答by araqnid
It's all very well not "wanting to use" < and > with timestamps, but those operators can be converted into index scans, and a string-match... well, it can, but EWWWW.
这一切都很好，不是“想要使用”带有时间戳的 < 和 >，但是这些运算符可以转换为索引扫描和字符串匹配......好吧，它可以，但是 EWWWW。
Well, the error is occurring because you need to explicitly convert the timestamp to a string before using a string operation on it, e.g.:
好吧，发生错误是因为您需要在对其使用字符串操作之前将时间戳显式转换为字符串，例如：
date_checker::text LIKE '2011-01-%'
and I supposeyou could then create an index on (date_checker::text)and that expression would become an index scan but.... EWWWW.
我想你可以创建一个索引(date_checker::text)，该表达式将成为索引扫描，但是...... EWWWW。
回答by Peter Eisentraut
Perhaps the date_truncfunction would be more to your liking:
也许该date_trunc功能更符合您的喜好：
... WHERE date_trunc('month', my_table.date_checker) = '2011-01-01'
You can also put an index on that expression, if needed.
如果需要，您还可以在该表达式上放置索引。
回答by Scott Marlowe
If you need to do a comparison on some part of a timestamp, it's far better to use the EXTRACT()function. For example:
如果您需要对 a 的某些部分进行比较timestamp，最好使用该EXTRACT()函数。例如：
WHERE EXTRACT(YEAR FROM date_checker) = 2011
AND EXTRACT(MONTH FROM date_checker) = 1
Details of the different "fields" you can extract from a date are in the documentation.
您可以从日期中提取的不同“字段”的详细信息在文档中。
回答by Patrick
I don't believe you can do a likeon a date column without converting it to a string representation first.
我不相信您可以like在不先将日期列转换为字符串表示形式的情况下对日期列进行操作。
You can use the between query to select between two dates, for instance:
您可以使用 between 查询在两个日期之间进行选择，例如：
SELECT id FROM my_table WHERE date_checker BETWEEN '2011-01-01' AND '2011-02-01';
回答by KUMAR AYYAPPA
Try this:
尝试这个：
SELECT my_table.id
FROM my_table
WHERE CAST(my_table.date_checker AS VARCHAR) LIKE '2011-01-%';
