Fuzzy Buckets: PostgreSQL Archives

PostgreSQL

May 20, 2010

Overlapping dates revisited

select distinct s1.resident_slot_id 

from eclub.resident_slot s1,  eclub.resident_slot s2 

where 

s1.start_time < s2.end_time 

and 

s2.start_time < s1.end_time 

and 

s1.resident_id = s2.resident_id 

and 

s1.resident_slot_id != s2.resident_slot_id 

and s2.expired_date is null and s1.expired_date is null

Posted by pj at 04:21 PM | Comments (0)

October 19, 2009

PostgreSQL 7.4: Mathematical Functions and Operators

PostgreSQL: Documentation: Manuals: PostgreSQL 7.4: Mathematical Functions and Operators

-43

Posted by pj at 04:55 PM | Comments (0)

October 01, 2009

De-dupe but leave one of each with one SQL query

Using sick sub-queries:

delete from ug_abstract where ug_abstract_id in 

-- Get a list of duplicated rows based upon a comparison of the title

(

select ug.ug_abstract_id 
from ug_abstract ug, ug_abstract ag 
where ug.title = ag.title  and ug.ug_abstract_id != ag.ug_abstract_id and ug.ug_abstract_id not in 


-- Get the PK from one of each of the rows that have been duplicated
-- then exclude them from the delete run with a "not in"

(select max(ug.ug_abstract_id) as ug_abstract_id 
from ug_abstract ug, ug_abstract ag 
where ug.title = ag.title  and ug.ug_abstract_id != ag.ug_abstract_id group by ug.title) 


group by ug.title, ug.ug_abstract_id order by ug.title

)

Posted by pj at 04:23 PM | Comments (0)

September 25, 2009

Mental BST events SQL with cluster tags included

-- All dates are plus one hour to accomodate BST '1970-01-01 01:00:00 GMT'

select distinct e.event_id, e.event_name, e.event_start_date, e.event_start_time, t.asset_id, '1970-01-01 01:00:00 GMT'::timestamp + 
((e.event_start_date::bigint)::text)::interval as ma_date, 

-- Get the day from the date and pad it with a leading zero if less than 10

case when extract('day' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint)::text)::interval) < 10 then
'0'||extract('day' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint)::text)::interval)::text else 
extract('day' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint)::text)::interval)::text end 
as ma_day, 

-- Get the month from the date and pad it with a leading zero if less than 10

case when extract('month' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint)::text)::interval) < 10 then
'0'||extract('month' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint)::text)::interval)::text else 
extract('month' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint)::text)::interval)::text end 
as ma_month, 

-- Get the year from the date

extract('year' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint)::text)::interval) as ma_year,

-- Get the hours and minutes from the date + the start time and pad them with a leading zeros if less than 10

case when extract('hour' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint + (e.event_start_time * 60 * 60))::text)::interval) < 10 then 
'0'||extract('hour' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint + (e.event_start_time * 60 * 60))::text)::interval)::text
else extract('hour' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint + (e.event_start_time * 60 * 60))::text)::interval)::text end 
as start_hour,

case when extract('minute' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint + (e.event_start_time * 60 * 60))::text)::interval) < 10 then
'0'||extract('minute' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint + (e.event_start_time * 60 * 60))::text)::interval)::text
else extract('minute' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint + (e.event_start_time * 60 * 60))::text)::interval)::text end as start_min,

-- Get two tags

max(rc.research_cluster_name) as tag_one,
min(rc.research_cluster_name) as tag_two

from event e 

-- left join in the research cluster (tag) table

left join event_research_cluster_link ecl on (ecl.event_id = e.event_id and ecl.expired_date is null) 

left join research_cluster rc on (ecl.research_cluster_id = rc.research_cluster_id and ecl.expired_date is null), 

-- Join on the heading and transaction table (latter for the asset_id)

event_heading_link l, heading h, transaction t where l.event_id = e.event_id and l.heading_id = h.heading_id and l.heading_id in (2) 
and t.event_id = e.event_id and t.asset_id is not null

-- Check that the event_start_date is in British Summer Time

and ('1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint)::text)::interval between 

-- Get the last Sunday in March

date_trunc('month', (extract('year' from now()::timestamp)::text||'-03-01 00:00:00 GMT'::text)::timestamp + '1 month'::interval) - '1 day'::interval + (((1 - 7 - to_char(date_trunc('month', (extract('year' from now()::timestamp)::text||'-03-01 00:00:00 GMT'::text)::timestamp + '1 month'::interval) - '1 day'::interval,'D')::int) %7)||' days')::interval 

and 

-- Get the last Sunday in October

date_trunc('month', (extract('year' from now()::timestamp)::text||'-10-01 00:00:00 GMT'::text)::timestamp + '1 month'::interval) - '1 day'::interval + (((1 - 7 - to_char(date_trunc('month', (extract('year' from now()::timestamp)::text||'-10-01 00:00:00 GMT'::text)::timestamp + '1 month'::interval) - '1 day'::interval,'D')::int) %7)||' days')::interval )

-- This is an archive so the start date must be before now

and '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint)::text)::interval < current_timestamp

group by e.event_start_date, e.event_start_time, e.event_name, t.asset_id, e.event_id

order by e.event_start_date desc, e.event_start_time desc

Posted by pj at 02:32 PM | Comments (0)