calendar snippets

year_progress_bq.sql

 SELECT dt,
        DATETIME_DIFF(DATETIME(dt), FIRST_VALUE(DATETIME(dt)) OVER (w), SECOND) /
        DATETIME_DIFF(LAST_VALUE(DATETIME(dt)) OVER (wall), FIRST_VALUE(DATETIME(dt)) OVER (w), SECOND) AS progress
	 FROM UNNEST(GENERATE_DATE_ARRAY('2024-01-01', '2025-01-01')) AS dt
QUALIFY progress < 1
 WINDOW w AS ( ORDER BY dt ),
        wall AS ( ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
 ;

	SELECT t,
	       TIMESTAMP_DIFF(t, FIRST_VALUE(t) OVER (w), SECOND) /
	       TIMESTAMP_DIFF(LAST_VALUE(t) OVER (wall), FIRST_VALUE(t) OVER (w), SECOND) AS progress
		FROM UNNEST(GENERATE_TIMESTAMP_ARRAY('2024-01-01', '2025-01-01', INTERVAL 1 DAY)) AS t
 QUALIFY progress < 1
	WINDOW w AS ( ORDER BY t ),
	       wall AS ( ORDER BY t ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
 ;

year_progress_percent.rb

y = 2024
t0 = Time.new(y)
year_seconds = Time.new(y + 1) - t0
c = 100
c.times.map { t0 + (year_seconds / c * _1) }

year_progress_permil.rb

require 'date'
y = 2024
d0 = Date.new(y)
d1 = Date.new(y+1)
year_seconds = d1.to_time - d0.to_time
(d0...d1).map{ ((_1.to_time - d0.to_time) / year_seconds) }.each{ printf "%.3f\n", _1 }