Get the date of the first day of the week when the date you want is in the range of that week
-
Edit /etc/ksqldb/ksql-server.properties
-
Append this line:
ksql.extension.dir=/opt/confluent-ksqldb/ext/
-
Copy FirstDayOfTheWeek-x.y.z.jar to /opt/confluent-ksqldb/ext/
-
Restart ksqldb-server
-
Query properties using:
LIST PROPERTIES;
Ensure the existence of this property:
ksql.extension.dir | KSQL | SERVER | /opt/confluent-ksqldb/ext/
-
Query functions using:
SHOW FUNCTIONS;
Ensure the existence of this function:
FIRSTDAYOFTHEWEEK | DATE / TIME
-
Query function description using:
DESCRIBE FUNCTION FirstDayOfTheWeek;
Description:
Name : FIRSTDAYOFTHEWEEK
Author : Mohammad Reza Hassanzadeh (github.com/MohammadRhz)
Version : 1.0.1
Overview : Get the date of the first day of the week when the date you want is in the range of that week
Type : SCALAR
Jar : /opt/confluent-ksqldb/ext/DateTimeUtils-1.0.0.jar
Variations :
Variation : FIRSTDAYOFTHEWEEK(timestamp BIGINT, shift INT, truncateTime BOOLEAN)
Returns : BIGINT
Description : Returns timestamp of the first day of the week
timestamp : Unix timestamp in milliseconds
timezone : Timezone (set GMT as default)
shift : Useful when the first day of the week is other than Monday. Use -2 for Saturday and -1 for Sunday.
truncateTime: Makes Hour, Minute, Second, and Millisecond 0
Variation : FIRSTDAYOFTHEWEEK(dateTimeStr VARCHAR, inFormatPattern VARCHAR, outFormatPattern VARCHAR, shift INT, truncateTime BOOLEAN)
Returns : VARCHAR
Description : Returns a formatted string of the date(time) of the first day of the week
inFormatPattern: Format of the input dateTimeStr. e.g.: yyyy-MM-dd HH:mm:ss.fff
outFormatPattern: Format of the output dateTimeStr. e.g.: yyyy-MM-dd
timezone : Timezone (set GMT as default)
shift : Useful when the first day of the week is other than Monday. Use -2 for Saturday and -1 for Sunday.
truncateTime: Makes Hour, Minute, Second, and Millisecond 0
Variation : FIRSTDAYOFTHEWEEK(dateTimeStr VARCHAR, formatPattern VARCHAR, shift INT, truncateTime BOOLEAN)
Returns : VARCHAR
Description : Returns a formatted string of the date(time) of the first day of the week
dateTimeStr : A formatted date and time string. e.g.: 2022-10-27 22:19:01.000
formatPattern: Format of the input dateTimeStr (Returned format will be the same as the input format). e.g.: yyyy-MM-dd HH:mm:ss.fff
timezone : Timezone (set GMT as default)
shift : Useful when the first day of the week is other than Monday. Use -2 for Saturday and -1 for Sunday.
truncateTime: Makes Hour, Minute, Second, and Millisecond 0
Read the description above and take a look at the examples below:
-- 1666523533000 is Sunday, October 23, 2022 2:42:13 PM GMT+03:30
select FIRSTDAYOFTHEWEEK(1666523533000,'GMT',0,false) from test emit changes;
+-----------------------------------------------------------------------------+
|KSQL_COL_0 |
+-----------------------------------------------------------------------------+
|1666005133000 -- Monday, October 17, 2022 2:42:13 PM GMT+03:30
-- In case the week in your area starts on Saturday
select FIRSTDAYOFTHEWEEK(1666523533000,'GMT',-2,false) from test emit changes;
+-----------------------------------------------------------------------------+
|KSQL_COL_0 |
+-----------------------------------------------------------------------------+
|1666437133000 -- Saturday, October 22, 2022 2:42:13 PM GMT+03:30
select FIRSTDAYOFTHEWEEK(1666523533000,'GMT',0,true) from test emit changes;
+-----------------------------------------------------------------------------+
|KSQL_COL_0 |
+-----------------------------------------------------------------------------+
|1665952200000 -- Monday, October 17, 2022 12:00:00 AM GMT+03:30
select FIRSTDAYOFTHEWEEK('2022-10-23 12:13:14','yyyy-MM-dd HH:mm:ss','GMT',0,false) from test;
+-----------------------------------------------------------------------------+
|KSQL_COL_0 |
+-----------------------------------------------------------------------------+
|2022-10-17 12:13:14
select FIRSTDAYOFTHEWEEK('2022-10-24 12:13:14','yyyy-MM-dd HH:mm:ss', 'yyyy-MM-dd HH:00:00','GMT',0,false) from test;
+-----------------------------------------------------------------------------+
|KSQL_COL_0 |
+-----------------------------------------------------------------------------+
|2022-10-24 12:00:00
select FIRSTDAYOFTHEWEEK('2022-10-25','yyyy-MM-dd', 'yyyy-MM-dd HH:00:00','GMT',0,false) from test;
+-----------------------------------------------------------------------------+
|KSQL_COL_0 |
+-----------------------------------------------------------------------------+
|2022-10-24 00:00:00
select FIRSTDAYOFTHEWEEK('2022-10-26 22','yyyy-MM-dd HH','GMT',0,false) from test;
+-----------------------------------------------------------------------------+
|KSQL_COL_0 |
+-----------------------------------------------------------------------------+
|2022-10-24 22
select FIRSTDAYOFTHEWEEK('2022-10-27 18:19:20','yyyy-MM-dd HH:mm:ss','GMT',0,true) from test;
+-----------------------------------------------------------------------------+
|KSQL_COL_0 |
+-----------------------------------------------------------------------------+
|2022-10-24 00:00:00
-- In case the week in your area starts on Saturday
select FIRSTDAYOFTHEWEEK('2022-10-28 18:19:20','yyyy-MM-dd HH:mm:ss','GMT',-2,true) from test;
+-----------------------------------------------------------------------------+
|KSQL_COL_0 |
+-----------------------------------------------------------------------------+
|2022-10-22 00:00:00