pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedduplicate dates
3+ messages / 3 participants
[nested] [flat]
* duplicate dates
@ 2003-08-01 15:56 Jodi Kanter <jkanter@virginia.edu>
2003-08-08 15:29 ` Re: duplicate dates Jonathan Gardner <jgardner@jonathangardner.net>
0 siblings, 1 reply; 3+ messages in thread
From: Jodi Kanter @ 2003-08-01 15:56 UTC (permalink / raw)
To: pgsql-sql
I have one table that has a date/time field in it. I'd like to identify
the records in the database where the date/time fields are the same. How
can I do this? Do I need to create a view or temp table? Is there a way
to run through one table multiple times.
Thanks.
Jodi
--
_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu <mailto:jkanter@virginia.edu>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: duplicate dates
2003-08-01 15:56 duplicate dates Jodi Kanter <jkanter@virginia.edu>
@ 2003-08-08 15:29 ` Jonathan Gardner <jgardner@jonathangardner.net>
0 siblings, 0 replies; 3+ messages in thread
From: Jonathan Gardner @ 2003-08-08 15:29 UTC (permalink / raw)
To: Jodi Kanter <jkanter@virginia.edu>; pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Friday 01 August 2003 08:56, Jodi Kanter wrote:
> I have one table that has a date/time field in it. I'd like to identify
> the records in the database where the date/time fields are the same. How
> can I do this? Do I need to create a view or temp table? Is there a way
> to run through one table multiple times.
SELECT date_trunc('month', date_column), ...
GROUP BY date_trunc('month', date_column)
HAVING count(primary_key) > 1;
Replace date_trunc('month', date_column) with whatever you want to group it
by. Note that date_part can give you interesting groupings (all the
Decembers, all the 1st of the months, all the Fridays, etc...)
If you want to run through it several times, you can have a sub-select in the
from clause. You could also save the results in a temp table.
- --
Jonathan Gardner <jgardner@jonathangardner.net>
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQE/M8HgWgwF3QvpWNwRAsUNAKCLnB6vajJ8fuS7IRgp0pYxp6YaxgCg2qbk
juL5a4tM1la0zmP81PdxS/c=
=N8Q/
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: duplicate dates
@ 2003-08-01 15:57 Christopher Browne <cbbrowne@libertyrms.info>
0 siblings, 0 replies; 3+ messages in thread
From: Christopher Browne @ 2003-08-01 15:57 UTC (permalink / raw)
To: Jodi Kanter <jkanter@virginia.edu>; +Cc: pgsql-sql
Jodi Kanter wrote:
> I have one table that has a date/time field in it. I'd like to identify
> the records in the database where the date/time fields are the same. How
> can I do this? Do I need to create a view or temp table? Is there a way
> to run through one table multiple times.
If you plan to review the data multiple times, and the table is rather
large, then it probably makes sense to make a temp table.
select a.* into temp table my_dupes from
dated_table a, dated_table b
where a.event_on = b.event_on
-- Assuming that the primary key is on (field1, field2, field3)
and a.key_field1 <> b.key_field1
and a.key_field2 <> b.key_field2
and a.key_field3 <> b.key_field3;
You could then rummage through my_dupes as needed. Note that if there
are more than 2 simultaneous records, it will list all of them
multiple times :-(.
You may also want to be more precise about what you mean by 'date/time
fields are the same'. They go down to fractions of a second, so you
shouldn't have a huge number of collisions.
--
output = ("cbbrowne" "@" "libertyrms.info")
<http://dev6.int.libertyrms.com/;
Christopher Browne
(416) 646 3304 x124 (land)
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-08-08 15:29 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-01 15:56 duplicate dates Jodi Kanter <jkanter@virginia.edu>
2003-08-08 15:29 ` Jonathan Gardner <jgardner@jonathangardner.net>
2003-08-01 15:57 Re: duplicate dates Christopher Browne <cbbrowne@libertyrms.info>
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