non iso week

[tiboun]

Isoweek doesn't exists in duckdb. In order to implement it, one of the solution is the following

		WITH nb_days_before as (
			select count(*) over (order by u.unnest) - 1 as nb_days_before from unnest((select Generate_Series(concat(extract('year' from date.unnest), '-01-01')::DATE , concat(extract('year' from date.unnest), '-01-07')::DATE, INTERVAL 1 DAY)::DATE[])) u
		QUALIFY extract('dow' from u.unnest) = 6
		)
		SELECT
			cast(ceil((extract('doy' from date.unnest) - n.nb_days_before) / 7) as int) as sunday_week
		FROM nb_days_before as n

Given bigquery input,

WITH years_offset as (
    select * from unnest(GENERATE_ARRAY(0, 8, 1)) as y_offset
),
years_dates AS (
    SELECT GENERATE_DATE_ARRAY(
        cast((2016 + o.y_offset) || '-01-01' as date),
        cast((2016 + o.y_offset) || '-01-08' as date), 
        INTERVAL 1 DAY
    ) as dates from years_offset as o
    UNION ALL
    SELECT GENERATE_DATE_ARRAY(
        cast((2016 + o.y_offset) || '-12-24' as date), 
        cast((2016 + o.y_offset) || '-12-31' as date), 
        INTERVAL 1 DAY
    ) as dates from years_offset as o
)
SELECT
    date,
    EXTRACT(WEEK FROM date) AS week_sunday,
    EXTRACT(WEEK(SUNDAY) FROM date) AS week_sunday_2,
    EXTRACT(WEEK(MONDAY) FROM date) AS week_monday,
    EXTRACT(WEEK(TUESDAY) FROM date) AS week_tuesday,
    EXTRACT(WEEK(WEDNESDAY) FROM date) AS week_wednesday,
    EXTRACT(WEEK(THURSDAY) FROM date) AS week_thursday,
    EXTRACT(WEEK(FRIDAY) FROM date) AS week_friday,
    EXTRACT(WEEK(SATURDAY) FROM date) AS week_saturday,
    EXTRACT(ISOWEEK FROM date) AS week_monday FROM years_dates, unnest(years_dates.dates) as date
order by date

we can transpile it to:

with years_offset as (
	select unnest(GENERATE_SERIES(0, 8, 1)) as y_offset 
),
years_dates AS (
    SELECT GENERATE_SERIES(
        cast((2016 + o.y_offset) || '-01-01' as date),
        cast((2016 + o.y_offset) || '-01-08' as date), 
        INTERVAL 1 DAY
    ) as dates from years_offset as o
    UNION ALL
    SELECT GENERATE_SERIES(
        cast((2016 + o.y_offset) || '-12-24' as date), 
        cast((2016 + o.y_offset) || '-12-31' as date), 
        INTERVAL 1 DAY
    ) as dates from years_offset as o
)
select 
	date.unnest as date,
	(
		WITH nb_days_before as (
			select count(*) over (order by u.unnest) - 1 as nb_days_before from unnest((select Generate_Series(concat(extract('year' from date.unnest), '-01-01')::DATE , concat(extract('year' from date.unnest), '-01-07')::DATE, INTERVAL 1 DAY)::DATE[])) u
		QUALIFY extract('dow' from u.unnest) = 0
		)
		SELECT
			cast(ceil((extract('doy' from date.unnest) - n.nb_days_before) / 7) as int) as sunday_week
		FROM nb_days_before as n
	) as week_sunday,
	(
		WITH nb_days_before as (
			select count(*) over (order by u.unnest) - 1 as nb_days_before from unnest((select Generate_Series(concat(extract('year' from date.unnest), '-01-01')::DATE , concat(extract('year' from date.unnest), '-01-07')::DATE, INTERVAL 1 DAY)::DATE[])) u
		QUALIFY extract('dow' from u.unnest) = 0
		)
		SELECT
			cast(ceil((extract('doy' from date.unnest) - n.nb_days_before) / 7) as int) as sunday_week
		FROM nb_days_before as n
	) as week_sunday_2,
	(
		WITH nb_days_before as (
			select count(*) over (order by u.unnest) - 1 as nb_days_before from unnest((select Generate_Series(concat(extract('year' from date.unnest), '-01-01')::DATE , concat(extract('year' from date.unnest), '-01-07')::DATE, INTERVAL 1 DAY)::DATE[])) u
		QUALIFY extract('dow' from u.unnest) = 1
		)
		SELECT
			cast(ceil((extract('doy' from date.unnest) - n.nb_days_before) / 7) as int) as sunday_week
		FROM nb_days_before as n
	) as week_monday,
	(
		WITH nb_days_before as (
			select count(*) over (order by u.unnest) - 1 as nb_days_before from unnest((select Generate_Series(concat(extract('year' from date.unnest), '-01-01')::DATE , concat(extract('year' from date.unnest), '-01-07')::DATE, INTERVAL 1 DAY)::DATE[])) u
		QUALIFY extract('dow' from u.unnest) = 2
		)
		SELECT
			cast(ceil((extract('doy' from date.unnest) - n.nb_days_before) / 7) as int) as sunday_week
		FROM nb_days_before as n
	) as week_tuesday,
	(
		WITH nb_days_before as (
			select count(*) over (order by u.unnest) - 1 as nb_days_before from unnest((select Generate_Series(concat(extract('year' from date.unnest), '-01-01')::DATE , concat(extract('year' from date.unnest), '-01-07')::DATE, INTERVAL 1 DAY)::DATE[])) u
		QUALIFY extract('dow' from u.unnest) = 3
		)
		SELECT
			cast(ceil((extract('doy' from date.unnest) - n.nb_days_before) / 7) as int) as sunday_week
		FROM nb_days_before as n
	) as week_wednesday,
	(
		WITH nb_days_before as (
			select count(*) over (order by u.unnest) - 1 as nb_days_before from unnest((select Generate_Series(concat(extract('year' from date.unnest), '-01-01')::DATE , concat(extract('year' from date.unnest), '-01-07')::DATE, INTERVAL 1 DAY)::DATE[])) u
		QUALIFY extract('dow' from u.unnest) = 4
		)
		SELECT
			cast(ceil((extract('doy' from date.unnest) - n.nb_days_before) / 7) as int) as sunday_week
		FROM nb_days_before as n
	) as week_thursday,
	(
		WITH nb_days_before as (
			select count(*) over (order by u.unnest) - 1 as nb_days_before from unnest((select Generate_Series(concat(extract('year' from date.unnest), '-01-01')::DATE , concat(extract('year' from date.unnest), '-01-07')::DATE, INTERVAL 1 DAY)::DATE[])) u
		QUALIFY extract('dow' from u.unnest) = 5
		)
		SELECT
			cast(ceil((extract('doy' from date.unnest) - n.nb_days_before) / 7) as int) as sunday_week
		FROM nb_days_before as n
	) as week_friday,
	(
		WITH nb_days_before as (
			select count(*) over (order by u.unnest) - 1 as nb_days_before from unnest((select Generate_Series(concat(extract('year' from date.unnest), '-01-01')::DATE , concat(extract('year' from date.unnest), '-01-07')::DATE, INTERVAL 1 DAY)::DATE[])) u
		QUALIFY extract('dow' from u.unnest) = 6
		)
		SELECT
			cast(ceil((extract('doy' from date.unnest) - n.nb_days_before) / 7) as int) as sunday_week
		FROM nb_days_before as n
	) as week_saturday,
	extract(week from date.unnest) as iso_week
from years_dates, unnest(years_dates.dates) as date
order by date;

The expected output is

date	week_sunday	week_sunday_2	week_monday	week_tuesday	week_wednesday	week_thursday	week_friday	week_saturday	week_monday_1
01/01/2016	0	0	0	0	0	0	1	0	53
02/01/2016	0	0	0	0	0	0	1	1	53
03/01/2016	1	1	0	0	0	0	1	1	53
04/01/2016	1	1	1	0	0	0	1	1	1
05/01/2016	1	1	1	1	0	0	1	1	1
06/01/2016	1	1	1	1	1	0	1	1	1
07/01/2016	1	1	1	1	1	1	1	1	1
08/01/2016	1	1	1	1	1	1	2	1	1
24/12/2016	51	51	51	51	51	51	52	52	51
25/12/2016	52	52	51	51	51	51	52	52	51
26/12/2016	52	52	52	51	51	51	52	52	52
27/12/2016	52	52	52	52	51	51	52	52	52
28/12/2016	52	52	52	52	52	51	52	52	52
29/12/2016	52	52	52	52	52	52	52	52	52
30/12/2016	52	52	52	52	52	52	53	52	52
31/12/2016	52	52	52	52	52	52	53	53	52
01/01/2017	1	1	0	0	0	0	0	0	52
02/01/2017	1	1	1	0	0	0	0	0	1
03/01/2017	1	1	1	1	0	0	0	0	1
04/01/2017	1	1	1	1	1	0	0	0	1
05/01/2017	1	1	1	1	1	1	0	0	1
06/01/2017	1	1	1	1	1	1	1	0	1
07/01/2017	1	1	1	1	1	1	1	1	1
08/01/2017	2	2	1	1	1	1	1	1	1
24/12/2017	52	52	51	51	51	51	51	51	51
25/12/2017	52	52	52	51	51	51	51	51	52
26/12/2017	52	52	52	52	51	51	51	51	52
27/12/2017	52	52	52	52	52	51	51	51	52
28/12/2017	52	52	52	52	52	52	51	51	52
29/12/2017	52	52	52	52	52	52	52	51	52
30/12/2017	52	52	52	52	52	52	52	52	52
31/12/2017	53	53	52	52	52	52	52	52	52
01/01/2018	0	0	1	0	0	0	0	0	1
02/01/2018	0	0	1	1	0	0	0	0	1
03/01/2018	0	0	1	1	1	0	0	0	1
04/01/2018	0	0	1	1	1	1	0	0	1
05/01/2018	0	0	1	1	1	1	1	0	1
06/01/2018	0	0	1	1	1	1	1	1	1
07/01/2018	1	1	1	1	1	1	1	1	1
08/01/2018	1	1	2	1	1	1	1	1	2
24/12/2018	51	51	52	51	51	51	51	51	52
25/12/2018	51	51	52	52	51	51	51	51	52
26/12/2018	51	51	52	52	52	51	51	51	52
27/12/2018	51	51	52	52	52	52	51	51	52
28/12/2018	51	51	52	52	52	52	52	51	52
29/12/2018	51	51	52	52	52	52	52	52	52
30/12/2018	52	52	52	52	52	52	52	52	52
31/12/2018	52	52	53	52	52	52	52	52	1
01/01/2019	0	0	0	1	0	0	0	0	1
02/01/2019	0	0	0	1	1	0	0	0	1
03/01/2019	0	0	0	1	1	1	0	0	1
04/01/2019	0	0	0	1	1	1	1	0	1
05/01/2019	0	0	0	1	1	1	1	1	1
06/01/2019	1	1	0	1	1	1	1	1	1
07/01/2019	1	1	1	1	1	1	1	1	2
08/01/2019	1	1	1	2	1	1	1	1	2
24/12/2019	51	51	51	52	51	51	51	51	52
25/12/2019	51	51	51	52	52	51	51	51	52
26/12/2019	51	51	51	52	52	52	51	51	52
27/12/2019	51	51	51	52	52	52	52	51	52
28/12/2019	51	51	51	52	52	52	52	52	52
29/12/2019	52	52	51	52	52	52	52	52	52
30/12/2019	52	52	52	52	52	52	52	52	1
31/12/2019	52	52	52	53	52	52	52	52	1
01/01/2020	0	0	0	0	1	0	0	0	1
02/01/2020	0	0	0	0	1	1	0	0	1
03/01/2020	0	0	0	0	1	1	1	0	1
04/01/2020	0	0	0	0	1	1	1	1	1
05/01/2020	1	1	0	0	1	1	1	1	1
06/01/2020	1	1	1	0	1	1	1	1	2
07/01/2020	1	1	1	1	1	1	1	1	2
08/01/2020	1	1	1	1	2	1	1	1	2
24/12/2020	51	51	51	51	52	52	51	51	52
25/12/2020	51	51	51	51	52	52	52	51	52
26/12/2020	51	51	51	51	52	52	52	52	52
27/12/2020	52	52	51	51	52	52	52	52	52
28/12/2020	52	52	52	51	52	52	52	52	53
29/12/2020	52	52	52	52	52	52	52	52	53
30/12/2020	52	52	52	52	53	52	52	52	53
31/12/2020	52	52	52	52	53	53	52	52	53
01/01/2021	0	0	0	0	0	0	1	0	53
02/01/2021	0	0	0	0	0	0	1	1	53
03/01/2021	1	1	0	0	0	0	1	1	53
04/01/2021	1	1	1	0	0	0	1	1	1
05/01/2021	1	1	1	1	0	0	1	1	1
06/01/2021	1	1	1	1	1	0	1	1	1
07/01/2021	1	1	1	1	1	1	1	1	1
08/01/2021	1	1	1	1	1	1	2	1	1
24/12/2021	51	51	51	51	51	51	52	51	51
25/12/2021	51	51	51	51	51	51	52	52	51
26/12/2021	52	52	51	51	51	51	52	52	51
27/12/2021	52	52	52	51	51	51	52	52	52
28/12/2021	52	52	52	52	51	51	52	52	52
29/12/2021	52	52	52	52	52	51	52	52	52
30/12/2021	52	52	52	52	52	52	52	52	52
31/12/2021	52	52	52	52	52	52	53	52	52
01/01/2022	0	0	0	0	0	0	0	1	52
02/01/2022	1	1	0	0	0	0	0	1	52
03/01/2022	1	1	1	0	0	0	0	1	1
04/01/2022	1	1	1	1	0	0	0	1	1
05/01/2022	1	1	1	1	1	0	0	1	1
06/01/2022	1	1	1	1	1	1	0	1	1
07/01/2022	1	1	1	1	1	1	1	1	1
08/01/2022	1	1	1	1	1	1	1	2	1
24/12/2022	51	51	51	51	51	51	51	52	51
25/12/2022	52	52	51	51	51	51	51	52	51
26/12/2022	52	52	52	51	51	51	51	52	52
27/12/2022	52	52	52	52	51	51	51	52	52
28/12/2022	52	52	52	52	52	51	51	52	52
29/12/2022	52	52	52	52	52	52	51	52	52
30/12/2022	52	52	52	52	52	52	52	52	52
31/12/2022	52	52	52	52	52	52	52	53	52
01/01/2023	1	1	0	0	0	0	0	0	52
02/01/2023	1	1	1	0	0	0	0	0	1
03/01/2023	1	1	1	1	0	0	0	0	1
04/01/2023	1	1	1	1	1	0	0	0	1
05/01/2023	1	1	1	1	1	1	0	0	1
06/01/2023	1	1	1	1	1	1	1	0	1
07/01/2023	1	1	1	1	1	1	1	1	1
08/01/2023	2	2	1	1	1	1	1	1	1
24/12/2023	52	52	51	51	51	51	51	51	51
25/12/2023	52	52	52	51	51	51	51	51	52
26/12/2023	52	52	52	52	51	51	51	51	52
27/12/2023	52	52	52	52	52	51	51	51	52
28/12/2023	52	52	52	52	52	52	51	51	52
29/12/2023	52	52	52	52	52	52	52	51	52
30/12/2023	52	52	52	52	52	52	52	52	52
31/12/2023	53	53	52	52	52	52	52	52	52
01/01/2024	0	0	1	0	0	0	0	0	1
02/01/2024	0	0	1	1	0	0	0	0	1
03/01/2024	0	0	1	1	1	0	0	0	1
04/01/2024	0	0	1	1	1	1	0	0	1
05/01/2024	0	0	1	1	1	1	1	0	1
06/01/2024	0	0	1	1	1	1	1	1	1
07/01/2024	1	1	1	1	1	1	1	1	1
08/01/2024	1	1	2	1	1	1	1	1	2
24/12/2024	51	51	52	52	51	51	51	51	52
25/12/2024	51	51	52	52	52	51	51	51	52
26/12/2024	51	51	52	52	52	52	51	51	52
27/12/2024	51	51	52	52	52	52	52	51	52
28/12/2024	51	51	52	52	52	52	52	52	52
29/12/2024	52	52	52	52	52	52	52	52	52
30/12/2024	52	52	53	52	52	52	52	52	1
31/12/2024	52	52	53	53	52	52	52	52	1

[tiboun]

This enable date_diff transpilation as well and fix approximation in:

SELECT
    DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', DAY) AS days_diff,
    DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', WEEK) AS weeks_diff;

To be transpiled as:

SELECT
	DATE_DIFF('DAY',
	DATE '2017-10-14',
	DATE '2017-10-15') AS days_diff,
	/* Approximation: WEEK is not distinct */
	(
    	WITH nb_days_before as (
			select count(*) over (order by u.unnest) - 1 as nb_days_before from unnest((select Generate_Series(concat(extract('year' from DATE '2017-10-15'), '-01-01')::DATE , concat(extract('year' from DATE '2017-10-15'), '-01-07')::DATE, INTERVAL 1 DAY)::DATE[])) u
		QUALIFY extract('dow' from u.unnest) = 0
		)
		SELECT
			cast(ceil((extract('doy' from DATE '2017-10-15') - n.nb_days_before) / 7) as int) as sunday_week
		FROM nb_days_before as n
	) - (
    	WITH nb_days_before as (
			select count(*) over (order by u.unnest) - 1 as nb_days_before from unnest((select Generate_Series(concat(extract('year' from DATE '2017-10-14'), '-01-01')::DATE , concat(extract('year' from DATE '2017-10-14'), '-01-07')::DATE, INTERVAL 1 DAY)::DATE[])) u
		QUALIFY extract('dow' from u.unnest) = 0
		)
		SELECT
			cast(ceil((extract('doy' from DATE '2017-10-14') - n.nb_days_before) / 7) as int) as sunday_week
		FROM nb_days_before as n
	) AS weeks_diff

Output is 1 and 1 instead of current transpilation 1 and 0.

[tiboun]

same for date_trunc and timestamp_trunc.