problems with timestamp fields

Started by Simon Crutealmost 25 years ago2 messagesgeneral
Jump to latest
#1Simon Crute
simon-news@nospam.geordie.demon.co.uk

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.

#2Simon Crute
simon-news@nospam.geordie.demon.co.uk
In reply to: Simon Crute (#1)
Re: problems with timestamp fields

Sorted it now.
I'm getting confused with to_date and to_timestamp again.

Ahhhh.
Thanks to anyone who looked at it anyway.