PostgreSQL:选择时间戳字段上的数据

本文介绍了PostgreSQL:选择时间戳字段上的数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试在日期字段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.