52

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'
MPelletier
  • 16,256
  • 15
  • 86
  • 137
Kevin Campion
  • 2,223
  • 2
  • 23
  • 29

5 Answers5

112

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.

araqnid
  • 127,052
  • 24
  • 157
  • 134
  • 3
    I think if index scans are you primary concern, you'd probably have a better change with the `LIKE` approach. The selectivity estimations of inequalities can fall down pretty quickly. Just saying ... ;-) – Peter Eisentraut Jan 26 '11 at 05:14
  • Maybe it would be better to use ::varchar? – kaleb4eg Aug 17 '16 at 14:54
13

Try this:

SELECT my_table.id
FROM my_table
WHERE CAST(my_table.date_checker AS VARCHAR) LIKE '2011-01-%';
alxlives
  • 5,084
  • 4
  • 28
  • 50
KUMAR AYYAPPA
  • 131
  • 1
  • 2
8

Perhaps the date_trunc function would be more to your liking:

... WHERE date_trunc('month', my_table.date_checker) = '2011-01-01'

You can also put an index on that expression, if needed.

Peter Eisentraut
  • 35,221
  • 12
  • 85
  • 90
7

If you need to do a comparison on some part of a timestamp, it's far better to use the EXTRACT() function. For example:

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.

Dan Getz
  • 8,774
  • 6
  • 30
  • 64
Scott Marlowe
  • 8,490
  • 3
  • 23
  • 21
  • 1
    or date_trunc if you need something like the first x part of a date. you can turn '2011-01-13 12:15:23' into '2011-01-01 00:00:00' – Scott Marlowe Jan 26 '11 at 08:18
  • Docs for the function suggested by Scott: [`date_trunc`](https://www.postgresql.org/docs/12/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC) (Postgres 12) – Jonathon Reinhart Nov 17 '21 at 03:45
4

I don't believe you can do a like on a date column without converting it to a string representation first.

You can use the between query to select between two dates, for instance:

SELECT id FROM my_table WHERE date_checker BETWEEN '2011-01-01' AND '2011-02-01';
Patrick
  • 17,669
  • 6
  • 70
  • 85