[Solved] Date Function ? Find specific days of week
[Solved] Date Function ? Find specific days of week
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
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
Last edited by robleyd on Tue Dec 24, 2019 11:40 pm, edited 2 times in total.
Reason: Add green tick
Reason: Add green tick
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: Date Function ? Find specific days of week
I have a HSQL2 table with consecutive dates.
returns this year's last 2 weeks:
You see that there is an inconsistency in HSQL? The weeknumber changes on Mondays whereas the week start (day 1) is always a Sunday.
modifies the built-in dayofweek so it returns 0 for Mondays and 6 for Sundays.
We subtract the above MondayStart from the date and get the Monday as "StartOfWeek".
We add (7-MondayStart) to the date and get the "EndtOfWeek"
Full query:
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 ) )
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
Code: Select all
MOD( DAYOFWEEK( "D" ) - 1, 7 ) "MondayStart"
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
Code: Select all
dateadd( 'dd', - MOD( DAYOFWEEK( "D" ) - 1, 7 )+1, "D" ) AS "StartOfWeek"
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
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. |
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Date Function ? Find specific days of week
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
mfg
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: Date Function ? Find specific days of week
Hi,
if i have to use HSQLDB 1.8.10, the embedded, there is no dateadd available.
What about this:
or over years ... but slower
R
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")
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
- MMove 1.0.6
- Extension for easy, exact positioning of shapes, pictures, controls, frames ...
- my current system
- Windows 10 AOO, LOLinux Mint AOO, LO
Re: Date Function ? Find specific days of week
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Date Function ? Find specific days of week
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
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",
...
...
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Date Function ? Find specific days of week
@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):
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"
Last edited by chrisb on Wed Dec 25, 2019 2:36 am, edited 3 times in total.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Re: Date Function ? Find specific days of week
Thanks guys, will put it to work.
Seasons Greetings
Seasons Greetings
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: [Solved] Date Function ? Find specific days of week
didn't like the slow DATEDIFF,
This works fast with HSQLDB 1.8.10 as well as with HSQLDB 2.50
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
- MMove 1.0.6
- Extension for easy, exact positioning of shapes, pictures, controls, frames ...
- my current system
- Windows 10 AOO, LOLinux Mint AOO, LO
Re: [Solved] Date Function ? Find specific days of week
... and requires a consecutive list of dates, all Mondays and Fridays at least.F3K Total wrote:didn't like the slow DATEDIFF,
This works fast with HSQLDB 1.8.10 as well as with HSQLDB 2.50
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Date Function ? Find specific days of week
Excellent gentlemen, very much appreciated
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: [Solved] Date Function ? Find specific days of week
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:Villeroy wrote:... 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 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:to find the Monday and Friday according to the given date.
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"
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
- MMove 1.0.6
- Extension for easy, exact positioning of shapes, pictures, controls, frames ...
- my current system
- Windows 10 AOO, LOLinux Mint AOO, LO
Re: [Solved] Date Function ? Find specific days of week
![Shocked :shock:](./images/smilies/icon_eek.gif)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice