Postgresql - query problem

Question

I have a variable which is in this format 2011-05-13. I want to make a query that adds one day to this variable and searches for days like this in the database.

I made this query but it doesnt work.

select phone from employee where date like (select date '2011-05-13' + 1) %

Can anyone help?

What type is the "date" field? What does it mean that the query "doesn't work?" Does it fail with a syntax error, because you meant to quote and concatenate that bare '%'? Does it fail with operator does not exist error because there's no overloaded LIKE operator that accepts a timestamp field and a string, and your query above isn't precisely what you're executing? — pilcrow
– pilcrow, Commented Jun 2, 2011 at 3:20

Frank Heikens · Accepted Answer · 2011-06-01 20:36:46Z

5

You need an INTERVAL:

SELECT phone FROM employee WHERE datefield = (date '2011-05-13' + INTERVAL '1 DAY') ;

Edit: Don't use LIKE when you're working with dates, there is no LIKE for a date.

answered Jun 1, 2011 at 20:36

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

tipotas Over a year ago

My date row is like this 2011-05-13 22:13:09.969 so i need % symbol to match 2011-05-13. I dont know how to do this. Adding 1 day to 2011-05-13 works for me with this query select date '2011-05-13' + 1.

invertedSpear Over a year ago

@tipotas: use between "where date between '2011-05-13' and '2011-05-14'" will get all the values on 5-13 as it will treat them as midnight to midnight.

Frank Heikens Over a year ago

Just cast your TIMESTAMP to a DATE: CAST(field AS DATE) = '2011-05-13'

Milen A. Radev Over a year ago

Seems like (according to OP) the column in question is of type text. I kinda doubt that but who knows. @tipotas: what type is the "date" column mentioned in your example query?

Frank Heikens Over a year ago

@Milen A. Radev: That would be a bug, he should fix that.

|

Imraan · Accepted Answer · 2011-06-01 20:44:50Z

0

Try the following:

SELECT phone FROM employee WHERE to_char(date, 'yyyy-mm-dd')::timestamp = ('2011-05-13'::timestamp + '1 day'::interval)

answered Jun 1, 2011 at 20:44

Imraan

6871 gold badge5 silver badges10 bronze badges

3 Comments

Peter Tillemans Over a year ago

YOu could also use date_trunc('day', date) instead of to_char().

Frank Heikens Over a year ago

Just CAST it to a DATE and you're fine. You don't need a string function to_char() to extract just the date part.

Frank Farmer Over a year ago

use of to_char() in this manner probably makes it impossible to use an index.

Collectives™ on Stack Overflow

Postgresql - query problem

2 Answers 2

7 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related