Paul Hollands: September 2009 Archives

« August 2009 | Main | October 2009 »

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)

September 24, 2009

Is this date during BST?


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

-- The 1 specifies Sunday

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

September 23, 2009

Code for detecting if it's BST

http://www.devscripts.net/browse/116.php

Posted by pj at 03:45 PM | Comments (0)

Getting a date from a bigint in PostgreSQL

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

Posted by pj at 12:19 PM | Comments (0)