PostgreSQL:选择时间戳字段上的数据 [英] PostgreSQL: Select data with a like on timestamp field

问题描述

我正在尝试在日期字段date_checked"(时间戳)上使用喜欢"从表中选择数据.但我有这个错误:

SQLSTATE[42883]:未定义函数:7 错误:运算符不存在:没有时区的时间戳

我的要求是:

SELECT my_table.idFROM my_tableWHERE my_table.date_checker LIKE '2011-01-%'

我不想使用:

SELECT my_table.idFROM my_tableWHERE my_table.date_checker >= '2011-01-01 00:00:00'AND my_table.date_checker <'2011-02-01 00:00:00'

解决方案

不想用"就好了

和 > 带有时间戳，但是这些运算符可以转换为索引扫描和字符串匹配...嗯，它可以，但是 EWWWW.

嗯，发生错误是因为您需要在对其使用字符串操作之前将时间戳显式转换为字符串，例如:

date_checker::text LIKE '2011-01-%'

我假设然后您可以在 (date_checker::text) 上创建一个索引，该表达式将成为索引扫描，但是.... EWWWW.

I am trying to select data from a table, using a "like" on date field "date_checked" (timestamp). But I have this error :

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'

解决方案

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.

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 suppose you could then create an index on (date_checker::text) and that expression would become an index scan but.... EWWWW.

这篇关于PostgreSQL:选择时间戳字段上的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！