pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to compare Datetime
2+ messages / 2 participants
[nested] [flat]
* How to compare Datetime
@ 1999-04-16 16:38 Henrik Pedersen <HenrikP@utp.dk>
0 siblings, 0 replies; 2+ messages in thread
From: Henrik Pedersen @ 1999-04-16 16:38 UTC (permalink / raw)
To: pgsql-sql
Hi...
Im having some problems with the datetime fields
Here is an example:
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| id | int4 | 4 |
| fornavn | text | var |
| tilmelddato | datetime | 8 |
+----------------------------------+----------------------------------+-------+
I want to select the entries that came in to the database 15 days ago.
tilmelddato contains the date that the entry was entered.
I do a "select * from testtable where tilmelddato = '01/04/1999'"
but that doesn't return anything even though my database contains
2 entries for that day.
If a do a "select * from testtable where tilmelddato > '01/04/1999'" it
returns 6 hits, but 2 of the hits was entered on the 01/04/1999 and
should not be in there, because of the >.
But if i use the < sign it works as i should. But if i do this:
"Select * from testtable where tilmelddato <= '15/04/1999'" the records
with 15/04/1999 is NOT included in the result set.
Im a bit confused about this .. the = dosnt work, the > and < works
partly
Im running Redhat 5.2 with kernel 2.2.4 and postgresql-6.4.2
Hope someone out there knows the answer :)
With friendly regards
Henrik Pedersen
Denmark
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] How to compare Datetime
@ 1999-04-17 15:46 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 1999-04-17 15:46 UTC (permalink / raw)
To: Henrik Pedersen <HenrikP@utp.dk>; +Cc: pgsql-sql@hub.org
Henrik Pedersen <HenrikP@utp.dk> writes:
> Im having some problems with the datetime fields
datetime is a date and time, not just a date. The behavior you describe
is all perfectly correct once you realize that your constants are being
interpreted as midnight of the given date.
If you only want to store the date, and not the time of day, your column
datatype should be "date" not datetime.
If you do want to store an exact time, then datetime is the right thing
to use. You can perform tests on it with one-day resolution by using
the date_part function to extract just the date. See the User's Guide.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1999-04-17 15:46 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-04-16 16:38 How to compare Datetime Henrik Pedersen <HenrikP@utp.dk>
1999-04-17 15:46 Re: [SQL] How to compare Datetime Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox