clock.sql

-- Open These views with QGIS
--   You can label the ticks view with this expression : "CASE WHEN m % 5 = 0 THEN h END" to get hour numbers
--   Set categorized styles on typ and unit to set different styles for dials, and for the hands
-- Please tweet your best clock designs with #PostGISClock hashtag

create or replace view ticks as
select 
	m as id
	, case when m % 5 = 0 then 'h' else 'm' end as typ
	, m % 5 as h
	, m as m
	, st_makepoint(
		cos(m::double precision * -pi() / 30.0 + pi() / 2)
		, sin(m::double precision * -pi() / 30.0 + pi() / 2)
	)
from 
	generate_series(1, 60) as m;

create or replace view clock as
with hms (id, unit, length, nb, val) as (
		values (1, 'h', 0.3, 12, extract('hour' from current_time))
			, (2, 'm', 0.6, 60, extract('minute' from current_time))
			, (3, 's', 0.9, 60, extract('second' from current_time))
	)
select 
	hms.id
	, st_makeline(
		st_makepoint(0, 0)
		, st_makepoint(
			hms.length * cos(val::double precision * -pi() / 6.0 + pi() / 2)
			, hms.length * sin(val::double precision * -pi() / 6.0 + pi() / 2)
		)
	) as geom
from 
	hms;