[Solved] Date Function ? Find specific days of week

[gkick]

Hi,

How does one associate the first and last day of the WEEK function in HSQL, so for instance if a timesheet is filtered by week 51 and you want a header in a report showing the period from the first day (Monday) of week 51 and the last day (Friday) of this week ?

Thks

[Villeroy]

I have a HSQL2 table with consecutive dates.

Code: Select all

SELECT "D", WEEK( "D" ) AS "W", DAYOFWEEK( "D" ) AS "DOW" FROM "TBL" WHERE ( YEAR( "D" ) = 2019 AND ( WEEK( "D" ) = 51 OR WEEK( "D" ) = 52 ) )

returns this year's last 2 weeks:

Code: Select all

D	W	DOW
2019-12-16 Mon	51	2
2019-12-17 Tue	51	3
2019-12-18 Wed	51	4
2019-12-19 Thu	51	5
2019-12-20 Fri	51	6
2019-12-21 Sat	51	7
2019-12-22 Sun	51	1
2019-12-23 Mon	52	2
2019-12-24 Tue	52	3
2019-12-25 Wed	52	4
2019-12-26 Thu	52	5
2019-12-27 Fri	52	6
2019-12-28 Sat	52	7
2019-12-29 Sun	52	1

You see that there is an inconsistency in HSQL? The weeknumber changes on Mondays whereas the week start (day 1) is always a Sunday.

Code: Select all

MOD( DAYOFWEEK( "D" ) - 1, 7 ) "MondayStart"

modifies the built-in dayofweek so it returns 0 for Mondays and 6 for Sundays.

Code: Select all

D	W	DOW	MondayStart
2019-12-16 Mon	51	2	1
2019-12-17 Tue	51	3	2
2019-12-18 Wed	51	4	3
2019-12-19 Thu	51	5	4
2019-12-20 Fri	51	6	5
2019-12-21 Sat	51	7	6
2019-12-22 Sun	51	1	0
2019-12-23 Mon	52	2	1
2019-12-24 Tue	52	3	2
2019-12-25 Wed	52	4	3
2019-12-26 Thu	52	5	4
2019-12-27 Fri	52	6	5
2019-12-28 Sat	52	7	6
2019-12-29 Sun	52	1	0

We subtract the above MondayStart from the date and get the Monday as "StartOfWeek".

Code: Select all

dateadd( 'dd', - MOD( DAYOFWEEK( "D" ) - 1, 7 )+1, "D" ) AS "StartOfWeek"

We add (7-MondayStart) to the date and get the "EndtOfWeek"

Code: Select all

dateadd( 'dd', 7 - MOD( DAYOFWEEK( "D" ) - 1, 7 ), "D" ) "EndOfWeek"

Code: Select all

D	W	DOW	MondayStart	StartOfWeek	EndOfWeek
2019-12-16 Mon	51	2	1	2019-12-16 Mon	2019-12-22 Sun
2019-12-17 Tue	51	3	2	2019-12-16 Mon	2019-12-22 Sun
2019-12-18 Wed	51	4	3	2019-12-16 Mon	2019-12-22 Sun
2019-12-19 Thu	51	5	4	2019-12-16 Mon	2019-12-22 Sun
2019-12-20 Fri	51	6	5	2019-12-16 Mon	2019-12-22 Sun
2019-12-21 Sat	51	7	6	2019-12-16 Mon	2019-12-22 Sun
2019-12-22 Sun	51	1	0	2019-12-23 Mon	2019-12-29 Sun
2019-12-23 Mon	52	2	1	2019-12-23 Mon	2019-12-29 Sun
2019-12-24 Tue	52	3	2	2019-12-23 Mon	2019-12-29 Sun
2019-12-25 Wed	52	4	3	2019-12-23 Mon	2019-12-29 Sun
2019-12-26 Thu	52	5	4	2019-12-23 Mon	2019-12-29 Sun
2019-12-27 Fri	52	6	5	2019-12-23 Mon	2019-12-29 Sun
2019-12-28 Sat	52	7	6	2019-12-23 Mon	2019-12-29 Sun
2019-12-29 Sun	52	1	0	2019-12-30 Mon	2020-01-05 Sun

Full query:

Code: Select all

SELECT "D", WEEK( "D" )AS "W", DAYOFWEEK( "D" )AS "DOW", 
MOD( DAYOFWEEK( "D" ) - 1, 7 )AS "MondayStart", 
dateadd( 'dd', - MOD( DAYOFWEEK( "D" ) - 1, 7 )+1, "D" )AS "StartOfWeek", 
dateadd( 'dd', 7 - MOD( DAYOFWEEK( "D" ) - 1, 7 ), "D" )AS "EndOfWeek" 
FROM "TBL" 
WHERE ( YEAR( "D" ) = 2019 AND ( WEEK( "D" ) = 51 OR WEEK( "D" ) = 52 ) )

Edit: P.S: There is still something wrong but I'm sure you get the idea.

[gkick]

Wow, thanks for that, a fair bit of work for just a report header... also some weird thing, the base query for the monthly report has a field called month right now showing 12 in the query, however the report shows 11 ???

mfg

[F3K Total]

Hi,
if i have to use HSQLDB 1.8.10, the embedded, there is no dateadd available.
What about this:

Code: Select all

SELECT 
    "MONDAYS"."D" MO,
    "FRIDAYS"."D" FR  
FROM  
    (SELECT "D" FROM "TBL" WHERE DAYOFWEEK( "D" ) = 6) "FRIDAYS" 
     JOIN 
    (SELECT "D" FROM "TBL" WHERE DAYOFWEEK( "D" ) = 2)"MONDAYS" 
     ON
     WEEK("FRIDAYS"."D") = WEEK("MONDAYS"."D")

or over years ... but slower

Code: Select all

SELECT 
    YEAR("D") "YEAR",
    WEEK("D")"WEEK",
    "MONDAYS"."D" MO,
    "FRIDAYS"."D" FR  
FROM  
    (SELECT "D" FROM "TBL" WHERE DAYOFWEEK( "D" ) = 6) "FRIDAYS" 
     JOIN 
    (SELECT "D" FROM "TBL" WHERE DAYOFWEEK( "D" ) = 2)"MONDAYS" 
     ON
     WEEK("FRIDAYS"."D") = WEEK("MONDAYS"."D")
    AND
    DATEDIFF('dd',"MONDAYS"."D","FRIDAYS"."D") = 4

R

[Villeroy]

Right. This is how we get Monday and Friday. We can ignore if the week start is Sunday or Monday since Monday and Friday always fall into the same week anyway. For the report header just group the report by MO and FR or concatenate MO and FR and group by that string.

[Villeroy]

Code: Select all

2015	52	21.12.15	25.12.15
2016	53	28.12.15	01.01.16
2016	1	04.01.16	08.01.16
2016	2	11.01.16	15.01.16

The solution has a week point when the Friday of the last week (52 or 53) overlaps into the next year.
Line #2 is the 53rd week to the old year 2015, not 2016.

Code: Select all

SELECT
    YEAR( "D") - case when Week("D")>51 AND Month("D")=1 then 1 else 0 end "YEAR",
...
...

[chrisb]

@gkick,
it's not feasible to use 'select ... where WEEK = x and YEAR = YYYY' because WEEK for year will very often apply to both January & December.
e.g. the dates 2019-01-01 through to 2019-01-06 & 2019-12-30 to 2019-12-31 are all week 1 for year 2019.

when using hsqldb 2.x your objective can be achieved as below.
hit the 'SQL' tool bar icon before executing.
NOTE: the week always begins on a Monday. the week may end on a Sunday or Friday (your choice):

Edit: 25 Dec 2019 01:25 cleaned up case when structures. no longer requires direct mode.

Code: Select all

--replace all instances of "Date" with the name of your date field
--replace all instances of "tDayOfWeek" with the name of your table
select to_char("Date", 'DY DD MON YYYY') "Date", --source date
--weekstart mon
to_char(
dateadd('dd',
case
	when dayofweek("Date") >1
		then 2 - dayofweek("Date")
	else - 6
end
,"Date"), 'DY DD MON YYYY "wk" IW')
"WeekStart"
,
--weekend sun
to_char(
dateadd('dd',
case
	when dayofweek("Date") >1
		then 8 - dayofweek("Date") 
	else 0
end
,"Date"), 'DY DD MON YYYY "wk" IW')
"WeekEndSunday"
,
--weekend fri
to_char(
dateadd('dd',
case
	when dayofweek("Date") >1
		then 6 - dayofweek("Date") 
	else - 2
end
,"Date"), 'DY DD MON YYYY "wk" IW')
"WeekEndFriday"
from "tDayOfWeek"
order by "tDayOfWeek"."Date"

[gkick]

Thanks guys, will put it to work.
Seasons Greetings

[F3K Total]

didn't like the slow DATEDIFF,
This works fast with HSQLDB 1.8.10 as well as with HSQLDB 2.50

Code: Select all

SELECT
    YEAR( "D" ) "YEAR",
    WEEK( "D" ) "WEEK",
    "MONDAYS"."D" "MO",
    "FRIDAYS"."D" "FR"
FROM
    ( SELECT "D" FROM "TBL" WHERE DAYOFWEEK( "D" ) = 6 ) "FRIDAYS"
JOIN
    ( SELECT "D" FROM "TBL" WHERE DAYOFWEEK( "D" ) = 2 ) "MONDAYS"
ON
    WEEK( "FRIDAYS"."D" ) = WEEK( "MONDAYS"."D" )
AND
    "FRIDAYS"."D" > "MONDAYS"."D"
AND
    YEAR( "FRIDAYS"."D" ) - YEAR( "MONDAYS"."D" ) BETWEEN 0 AND 1

[Villeroy]

didn't like the slow DATEDIFF,
This works fast with HSQLDB 1.8.10 as well as with HSQLDB 2.50

... and requires a consecutive list of dates, all Mondays and Fridays at least.
The HSQL2 solution with DATEADD calculates the upper and lower bounds for any date even if the matching Monday and Friday are not stored in the table.

[gkick]

Excellent gentlemen, very much appreciated

[F3K Total]

... and requires a consecutive list of dates, all Mondays and Fridays at least.
The HSQL2 solution with DATEADD calculates the upper and lower bounds for any date even if the matching Monday and Friday are not stored in the table.

you're right. So i tried to find a solution without using DATEADD, as HSQL1.8.10 doesn't have it, it's possible, but more complex:

• You can under HSQL 1.8.10 create a calendar for let's say 5 years, current date plus/minus two years, using this query, named Q_CALENDAR (direct SQL-Mode):

  Code: Select all

  SELECT DISTINCT
      CAST(  Y.Z + YEAR(CURRENT_DATE) - 3
      || '-'
      || CASEWHEN( M.Z < 10, '0' || M.Z, '' || M.Z )
      || '-'
      || CASEWHEN( D.Z  < 10, '0' || D.Z , '' || D.Z  ) AS DATE )
      "D"
  FROM
      (SELECT ORDINAL_POSITION AS Z FROM INFORMATION_SCHEMA.SYSTEM_COLUMNS where TABLE_NAME ='SYSTEM_ALLTYPEINFO' AND ORDINAL_POSITION <= 5) AS Y ,
      (SELECT ORDINAL_POSITION AS Z FROM INFORMATION_SCHEMA.SYSTEM_COLUMNS where TABLE_NAME ='SYSTEM_ALLTYPEINFO' AND ORDINAL_POSITION <= 12) AS M ,
      (SELECT ORDINAL_POSITION AS Z FROM INFORMATION_SCHEMA.SYSTEM_COLUMNS where TABLE_NAME ='SYSTEM_ALLTYPEINFO' AND ORDINAL_POSITION <= 31) AS D

• Then rank all days in this period by using query Q_RANK_WEEKDAY

  Code: Select all

  SELECT COUNT( * ) "RANK", "X"."D" FROM "Q_CALENDAR" "X" LEFT JOIN "Q_CALENDAR" "Y" ON "X"."D" >= "Y"."D" GROUP BY "X"."D"

• Then calculate the difference-days from Dates out of a given Dates table "TBL" by query Q_DELTA_DAYS, using "case when" shown by crisb:

  Code: Select all

  SELECT "D", CASE WHEN DAYOFWEEK( "D" ) > 1 THEN 2 - DAYOFWEEK( "D" ) ELSE - 6 END "WEEKSTART_MONDAY", CASE WHEN DAYOFWEEK( "D" ) > 1 THEN 6 - DAYOFWEEK( "D" ) ELSE - 2 END "WEEKEND_FRIDAY" FROM "TBL"

• Now transfer these delta-days to the rank query in "Q_DELTA_RANK"

  Code: Select all

  SELECT "Q_DELTA_DAYS"."D", "Q_DELTA_DAYS"."WEEKSTART_MONDAY" + "Q_RANK_WEEKDAY"."RANK" "WEEKRANK_MONDAY", "Q_DELTA_DAYS"."WEEKEND_FRIDAY" + "Q_RANK_WEEKDAY"."RANK" "WEEKRANK_FRIDAY" FROM "Q_DELTA_DAYS" LEFT JOIN "Q_RANK_WEEKDAY" ON "Q_DELTA_DAYS"."D" = "Q_RANK_WEEKDAY"."D"

• And in the end, using Q_MONDAYS_FRIDAYS:

  Code: Select all

  SELECT "Q_DELTA_RANK"."D", "Q_RANK_WEEKDAY"."D" AS "MO", "Q_RANK_WEEKDAY_1"."D" AS "FR" FROM  "Q_DELTA_RANK" LEFT  JOIN "Q_RANK_WEEKDAY" ON "Q_DELTA_RANK"."WEEKRANK_MONDAY" = "Q_RANK_WEEKDAY"."RANK" LEFT JOIN "Q_RANK_WEEKDAY" AS "Q_RANK_WEEKDAY_1" ON "Q_DELTA_RANK"."WEEKRANK_FRIDAY" = "Q_RANK_WEEKDAY_1"."RANK" 

  to find the Monday and Friday according to the given date.

These queries need nearly the same time for calculating as the datadiff function, so if anybody here has a faster solution, it would be much appreciated.
And Villeroy, i know, it's not as complete as using the dateadd in HSQL 250, 'cause only for 5 years, but in most cases it should be enough.
R

[Villeroy]

Wow. The surprising thing is how you generate 3 sequences from arbitrary numbers which can be found in the database anyway and how the concatenated non-dates (31st of February) are simply ignored.