col > 'YYYY-08-31' is showing data of 31st Aug also. Why?-CodePudding

I have a bookings table having the following columns:

bookid,facility_id,member_id,start_time,slots. datatype of start_time is timestamp without timezone.

when I use the following query to see all the bookings starting from 2012 September, I am getting some results of 2012 August also.

select start_time from cd.bookings
where start_time > '2012-08-31';

Sample Result:

"2012-08-31 09:00:00"
"2012-08-31 10:30:00"
"2012-08-31 12:00:00"
"2012-08-31 14:30:00"
"2012-08-31 16:30:00"
"2012-08-31 19:00:00"
"2012-08-31 08:00:00"
"2012-08-31 10:30:00"
"2012-08-31 12:00:00"
"2012-08-31 13:30:00"
"2012-08-31 15:00:00"
"2012-08-31 18:30:00"
"2012-08-31 08:30:00"
"2012-08-31 11:00:00"
"2012-08-31 12:30:00"
"2012-08-31 14:00:00"
"2012-08-31 17:00:00"
"2012-08-31 19:00:00"
"2012-08-31 09:00:00"
"2012-08-31 10:30:00"
"2012-08-31 12:30:00"
"2012-08-31 19:30:00"
"2012-08-31 08:30:00"
"2012-08-31 10:00:00"
"2012-08-31 12:30:00"
"2012-08-31 13:30:00"
"2012-08-31 14:30:00"
"2012-08-31 16:30:00"
"2012-08-31 18:00:00"
"2012-08-31 19:00:00"
"2012-08-31 09:30:00"
"2012-08-31 11:00:00"
"2012-08-31 15:00:00"
"2012-08-31 17:00:00"
"2012-08-31 09:00:00"
"2012-08-31 11:00:00"
"2012-08-31 14:30:00"
"2012-08-31 18:00:00"
"2012-08-31 08:00:00"
"2012-08-31 11:30:00"
"2012-08-31 13:00:00"
"2012-08-31 15:00:00"
"2012-08-31 16:30:00"
"2012-08-31 17:30:00"
"2012-08-31 18:30:00"
"2012-08-31 08:30:00"
"2012-08-31 10:00:00"
"2012-08-31 12:30:00"
"2012-08-31 13:30:00"
"2012-08-31 14:00:00"
"2012-08-31 14:30:00"
"2012-08-31 16:00:00"
"2012-08-31 16:30:00"
"2012-08-31 17:00:00"
"2012-08-31 18:00:00"
"2012-08-31 18:30:00"
"2012-08-31 19:00:00"
"2012-08-31 20:00:00"
"2012-09-01 08:00:00"
"2012-09-01 11:00:00"
"2012-09-01 12:30:00"
"2012-09-01 15:00:00"
"2012-09-01 17:00:00"
"2012-09-01 08:00:00"
"2012-09-01 09:30:00"
"2012-09-01 11:00:00"
"2012-09-01 14:30:00"
"2012-09-01 16:30:00"
"2012-09-01 19:00:00"
"2012-09-01 09:00:00"
"2012-09-01 13:30:00"
"2012-09-01 16:30:00"
"2012-09-01 18:00:00"
"2012-09-01 08:30:00"
"2012-09-01 09:30:00"
"2012-09-01 10:30:00"
"2012-09-01 12:30:00"
"2012-09-01 14:00:00"
"2012-09-01 15:00:00"
"2012-09-01 16:30:00" and so on.....

Why is that?

CodePudding user response：

Since the data type is timestamp, the literal '2012-08-30' is interpreted as 2012-01-01 00:00:00. To get only values with a timestamp from Sep 1 or later, use

WHERE start_time >= '2021-09-01 00:00:00'

CodePudding user response：

Because those are strings, not date/time objects. Just like "AAAA" is greater than "AAA", in this "2012-08-31 ANYTHING" is greater than "2012-08-31". Maybe you want start_time > "2012-08-31 23:59:59".