« Is this date during BST? | Main | De-dupe but leave one of each with one SQL query »
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
Tags: PostgreSQL
Posted by pj at September 25, 2009 02:32 PM