Thread: duplicate dates

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/timefields are the same. How can I do this? Do I need to create a view or temp table? Is there a way to run
throughone table multiple times.<br /> Thanks.<br /> Jodi<br /><div class="moz-signature">-- <br /></div><div
class="Section1"><pclass="MsoNormal"><i><span style="font-size: 9pt; font-family: Arial; ">
_______________________________<br/></span></i><i><span style="font-size: 10pt; ">Jodi L Kanter<br /> BioInformatics
DatabaseAdministrator<br /> University of Virginia<br /> (434) 924-2846<br /><a
href="mailto:jkanter@virginia.edu">jkanter@virginia.edu</a></span></i><span style="font-size: 11pt; font-family: Arial;
"><brstyle="mso-special-character:line-break" /><br style="mso-special-character:line-break" /></span><p
class="MsoNormal"><spanstyle="font-size: 11pt; font-family: Arial; ">  </span><p class="MsoNormal"><i><span
style="font-size:9pt; font-family: Arial; ">  </span></i><p class="MsoNormal"><i><span style="font-size: 9pt;
font-family:Arial; ">  </span></i></div> 

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    --
Assumingthat 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)

-----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-----