confusion in using select command in postgresql with timestamp column

Question

I have table which has structure like this.

CREATE TABLE users (
    id serial NOT NULL,
    created_at timestamp NOT NULL
    )

I have more than 1000 records in this table. This is my first query.

1 query

select id,created_at from users where id in  (1051,1052)

This returns two rows which is correct as as expected. However when I use

2nd Query

select id,created_at from users where  created_at = '2020-06-28'

or

select id,created_at from users where  created_at = date '2020-06-28'

It returns nothing, this is not expected result as it should return two rows against it.

Similarly if I use this

3rd Query

select id, created_at from users where created_at between date '2020-06-28' and date '2020-06-28'

It returns nothing however I think this should also return two rows.

While this

4th Query

select id, created_at from users where created_at between date '2020-06-28' and date '2020-06-29'

returns two rows.

Show timezone returns correct timezong in which currently i am

I did`t understand this, why the results are different in 2nd, 3rd and 4th query. How can i get same result as of query 1 using 3rd query.

Akhilesh Mishra · Accepted Answer · 2020-07-05 18:20:18Z

One single reason for all your queries is that you are comparing timestamp with date

in Query 2

You are comparing 2020-06-28 13:02:53 = 2020-06-28 00:00:00 which will not match so returning no records.

in Query 3

You are using between i.e. 2020-06-28 13:02:53 between 2020-06-28 00:00:00 and 2020-06-28 00:00:00 which will not match so returning no records.

in Query 4

You are using between i.e. 2020-06-28 13:02:53 between 2020-06-28 00:00:00 and 2020-06-29 00:00:00. Here both records are falling in those timestamps and you are getting the records

So you have to compare date values. As right operand is a date type value, you have to convert the left operand to date. try this

for 2nd Query

select id,created_at from users where  date(created_at) = '2020-06-28'

for 3rd Query

select id, created_at from users where date(created_at) between date '2020-06-28' and date '2020-06-28'

You should opt 3rd method if you want to compare a date range. For one day only you should use 2nd query.

Adrian Klaver · Accepted Answer · 2020-07-05 17:52:45Z

0

Because what you are doing is:

test(5432)=# select '2020-06-28'::timestamp;
      timestamp      
---------------------
 06/28/2020 00:00:00

You are selecting for created_at that is exactly at midnight and there is none. The same thing when you do:

select id, created_at from users where created_at between date '2020-06-28' and date '2020-06-28'

You already corrected the mistake in your 3rd query in the 4th query:

select id, created_at from users where created_at between date '2020-06-28' and date '2020-06-29'

where you span the time from midnight of 06/28/2020 to midnight 06/29/2020

An alternate solution is:

create table dt_test(id integer, ts_fld timestamp);
insert into dt_test values (1, '07/04/2020 8:00'), (2, '07/05/2020 1:00'), (3, '07/05/2020 8:15');
select * from dt_test ;
 id |       ts_fld        
----+---------------------
  1 | 07/04/2020 08:00:00
  2 | 07/05/2020 01:00:00
  3 | 07/05/2020 08:15:00

select * from dt_test where date_trunc('days', ts_fld) = '07/05/2020'::date;
 id |       ts_fld        
----+---------------------
  2 | 07/05/2020 01:00:00
  3 | 07/05/2020 08:15:00

In your case:

select id, created_at from users where date_trunc('days', created_at) = '2020-06-28'::date;

edited Jul 5, 2020 at 17:52

answered Jul 5, 2020 at 17:41

Adrian Klaver

20.4k3 gold badges24 silver badges40 bronze badges

2 Comments

kartoos khan Over a year ago

what about query2. Why cant i get results with this. I think it should return two rows.

Adrian Klaver Over a year ago

No you won't as I explained in the first example. created_at = '2020-06-28' becomes created_at = '06/28/2020 00:00:00' and there no such values to match.

Collectives™ on Stack Overflow

confusion in using select command in postgresql with timestamp column

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related