From: | "Simon Crute" <simon-news(at)nospam(dot)geordie(dot)demon(dot)co(dot)uk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | problems with timestamp fields |
Date: | 2001-05-13 17:44:44 |
Message-ID: | 989776100.15602.0.nnrp-08.9e981bad@news.demon.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | u95886230 | 2001-05-13 18:45:05 | sql question |
Previous Message | Ed Loehr | 2001-05-13 15:35:28 | Re: [PATCHES] Re: microsecond log timestamps |