Hi,
I'm sure the following is a bug in my understanding rarther than the code,
but I can't find anywhere in the docs that goes into enough deatails for me
to understand.
I think it's something to do with the timezone bit in the timestamp fields,
but I don't know how to fix it.
I've got a table that holds
RMS=# select * from bookings where resid=205;
bookid | resid | userid | start_date_time | end_date_time |
confirmed | cancelled | booked_date
--------+-------+--------+------------------------+------------------------+
-----------+-----------+------------------------
2010 | 205 | 22 | 2001-05-26 13:00:00+01 | 2001-05-26 14:00:00+01 |
N | N | 2001-05-13 10:51:18+01
2011 | 205 | 22 | 2001-05-26 13:00:00+01 | 2001-05-26 14:00:00+01 |
N | N | 2001-05-13 10:51:56+01
(2 rows)
When I run this query against it, it returns nothing.
SELECT
MIN(TO_CHAR(start_date_time, 'yyyy:mm:dd:hh24:mi')),
MAX(TO_CHAR(end_date_time,'yyyy:mm:dd:hh24:mi'))
FROM
bookings
WHERE
resid = '205'
AND
end_date_time > TO_DATE( '2001:05:26:13:00:00',
'yyyy:mm:dd:hh24:mi:ss')
AND
start_date_time < TO_DATE( '2001:05:26:14:00:00',
'yyyy:mm:dd:hh24:mi:ss')
min | max
-----+-----
|
(1 row)
It should have returned any reccords that overlapped, i.e. 2001-05-26
13:00:00, and 2001-05-26 14:00:00
What am I doing wrong ?
Thanks.
