problems with timestamp fields

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.

Responses

Browse pgsql-general by date

  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