The Wetonan Cycle

In Java Indonesia, at least 5 calendars are in use. The Gregorian, the Islamic and the Chinese which all have 12 months and 7 day weeks. Then there is the Pasaran calendar which also has 12 months, but that one has 5 day weeks. Pasaran comes from Pasar which means market, and in older times market traders would go from village to village in a 5 day cycle. Hence the name Market Week.

There is also the Hindu-Balinese Pawukon calendar which is so complex I will omit the explanation here since it is beyond the scope of the subect at hand.

And then there is the Wetonan Cycle, a combination of a 7 day week and the 5 day market week both running concurrent. So when the cycle starts, both start on the first day. So you would start with 1-1, 2-2, 3-3, 4-4, 5-5, but then you would get 6-1, 7-2, 1-3, 2-4 and so on. Since you are combining 5 and 7 day patterns, the Wetonan Cycle goes on for 35 days and then start again. The combination of the weekday name and the marketday name is the so called Wetonan Day.

So why is the Wetonan Cycle important for the Javanese?

Traditionally it is used to determine the right day on which celebrations and ceremonies should be held. And the Wetonan Day is somewhat similar to a star sign as it predicts the character of a person born on that day. In conjuction with a complex Number System, it is used to valdiate two people are a match for a marriage and to calculate the best day for the wedding.

So even today the Wetonan Cycle and the Wetonan Day play in important role in cultural and spiritual life. But how do you find out the Wetonan Day for your Birthdate? Could you do that with SQL?

That's what we are going to find out!

The issues at hand

My idea was to create a View with all the Dates for a certain period. And then find out per Date what the weekday is and what the marketday is, and use those to find the Wetonan Day.

There's a few challenges in that.

First of all, where to get the Dates for the given date range? Then how to get the marketday? Sure, SQL Server has lots of Date Functions, but they are all designed for a 7 day week.

And while building this Query, one Common Table Expression was not enough. I had to stack one on top of the other. So how to do that?

All this and more will come by while solving this case. Wherever possible, I have linked to earlier Blogposts on that subject.

Building the Query

Get all the Dates

First thing is to get a list of all the Dates for a certain period. Here the Generate_Series() Function which is new in SQL Server 2022 could solve that problem.

select
cast(dateadd(day, value, cast('1000-01-01' as datetime2)) as date) as TheDate
from generate_series(0, datediff(day, cast('1000-01-01' as datetime2), '2099-12-31'))

As you can see I used a DateTime2 since that one can go back further in time than the regular DateTime. We can use this one as a Common Table Expression so we can than further analyze the Dates we now have:

So far, so good. With these Dates it seems not that hard to get the day of the week. But to have the week start at Monday would require a "Set DateFirst 1" and that one is not allowed in a View. Luckily there is this trick:

select (datepart(Weekday, getdate()) + @@DATEFIRST + 5) % 7 + 1 as WeekDayNumber

Using this I could determine the weekday and use that to list the names in both Indonesian and Gregorian calendar.

Now to construct the Marketweek Days I thought I could make use of the Date_Bucket() Function which is also new in SQL Server 2022. DAte_Bucket() has an optional reference date you can supply that is the first day of the Marketweek. Luckily my Birthday this year was on such a day, so that one is easy to remember.

And with that we now have this:

Stacking Common Table Expressions

Here is where it gets tricky. I now have the MarketWeekStart Date, But I also need the MarketWeekEnd to determine the Weekday. For that I needed to stack a CTE on top of another CTE.

Here is the basic structure on how to do that:

with Details as(
  -- Some Query here
),
SubTotals as (
  -- Some Query here
),
GrandTotals as (
  -- Some Query here
)
select *
from GrandTotals

As you can see you only need the keywordt With once and the comma separate the next CTE you want to add.

with BaseDates as (
  select
  cast(dateadd(day, value, cast('1000-01-01' as datetime2)) as date) as TheDate
  from generate_series(0, datediff(day, cast('1000-01-01' as datetime2), '2099-12-31'))
),
Wetonan as (
  select *,
  (datepart(Weekday, TheDate) + @@DATEFIRST + 5) % 7 + 1 as WeekDayNumber,
  case (datepart(Weekday, TheDate) + @@DATEFIRST + 5) % 7 + 1
    when 1 then 'Senin'
    when 2 then 'Selasa'
    when 3 then 'Rabu'
    when 4 then 'Kamis'
    when 5 then 'Jumat'
    when 6 then 'Sabtu'
    when 7 then 'Mingu'
  end as WeekDayName,
  case (datepart(Weekday, TheDate) + @@DATEFIRST + 5) % 7 + 1
    when 1 then 'Monday'
    when 2 then 'Tuesday'
    when 3 then 'Wednesday'
    when 4 then 'Thursday'
    when 5 then 'Friday'
    when 6 then 'Saturday'
    when 7 then 'Sunday'
  end as GregorianName,
  date_bucket(Day, 5, TheDate, cast('2024-03-10' as date)) MarketWeekStart
  from BaseDates
)
select *,
lead(MarketWeekStart)
over (partition by MarketWeekStart order by MarketWeekStart) as MarketWeekEnd,
isnull(
datediff(Day, lead(MarketWeekStart)
over (partition by MarketWeekStart order by MarketWeekStart), TheDate) + 1, 5) as MarketDayNumber,
case isnull(
datediff(Day, lead(MarketWeekStart)
over (partition by MarketWeekStart order by MarketWeekStart), TheDate) + 1, 5)
  when 1 then 'Legi'
  when 2 then 'Pahing'
  when 3 then 'Pon'
  when 4 then 'Wage'
  when 5 then 'Kliwon'
end as MarketDayName
from Wetonan

As you can see I used the Lead() Function to grab the MarketWeekStart from the next row. Since I used a Date_Bucket() for 5 days, the fifth day cannot grab that one so there it will return Null.

Having this, I can use the DateDiff() Function to calculate the number of Days between the MarketWeekStart and MarketWeekEnd. I have add 1 since the first day would return a difference for 0 days. And since the DateDiff() Function would return Null for the fifth day, I put an IsNUll() around it with a default value of 5.

And now I know the weekday for the 5 day week, I might as well asign the Javanese name for it as well. With that I am almost done!

Combine the names I found

Now all that needs to be done is to combine the names from the Weekdays we have now. And since I don't want to copy the naming case, there is room for another CTE:

create or alter view vwWetonanDays as
with BaseDates as (
  select
  cast(dateadd(day, value, cast('1000-01-01' as datetime2)) as date) as TheDate
  from generate_series(0, datediff(day, cast('1000-01-01' as datetime2), '2099-12-31'))
),
Wetonan as (
  select *,
  (datepart(Weekday, TheDate) + @@DATEFIRST + 5) % 7 + 1 as WeekDayNumber,
  case (datepart(Weekday, TheDate) + @@DATEFIRST + 5) % 7 + 1
    when 1 then 'Senin'
    when 2 then 'Selasa'
    when 3 then 'Rabu'
    when 4 then 'Kamis'
    when 5 then 'Jumat'
    when 6 then 'Sabtu'
    when 7 then 'Mingu'
  end as WeekDayName,
  case (datepart(Weekday, TheDate) + @@DATEFIRST + 5) % 7 + 1
    when 1 then 'Monday'
    when 2 then 'Tuesday'
    when 3 then 'Wednesday'
    when 4 then 'Thursday'
    when 5 then 'Friday'
    when 6 then 'Saturday'
    when 7 then 'Sunday'
  end as GregorianName,
  date_bucket(Day, 5, TheDate, cast('2024-03-10' as date)) MarketWeekStart
  from BaseDates
),
WetonanDays as (
  select *,
  lead(MarketWeekStart)
  over (partition by MarketWeekStart order by MarketWeekStart) as MarketWeekEnd,
  isnull(
  datediff(Day, lead(MarketWeekStart)
  over (partition by MarketWeekStart order by MarketWeekStart), TheDate) + 1, 5) as MarketDayNumber,
  case isnull(
  datediff(Day, lead(MarketWeekStart)
  over (partition by MarketWeekStart order by MarketWeekStart), TheDate) + 1, 5)
    when 1 then 'Legi'
    when 2 then 'Pahing'
    when 3 then 'Pon'
    when 4 then 'Wage'
    when 5 then 'Kliwon'
  end as MarketDayName
  from Wetonan
)
select TheDate, concat(WeekDayName, ' ', MarketDayName) as WetonanDay, GregorianName,
WeekDayNumber, WeekDayName, MarketDayNumber, MarketDayName
from WetonanDays
GO

select *
from vwWetonanDays

View Performance

In this View I used quite a large Date Range resulting in over 400,000 records. Still, the performance is not bad at all. Then again, it certainly is not the best performance either.

And since every time I used it, it would produce the same result. So I decided to create a table and then dump the result into that only once. And then Query on that table instead of the View.

So here is the Table:

create table WetonanDays (
  TheDate datetime2 primary key,
  WetonanDay nvarchar(50),
  WeekDayNumber int,
  WeekDayName nvarchar(25),
  MarketDayNumber int,
  MarketDayName nvarchar(25),
  GregorianName nvarchar(25)
)
GO

And here is the insert for it:

insert into WetonanDays( TheDate, WetonanDay, WeekDayNumber, WeekDayName, MarketDayNumber, MarketDayName, GregorianName ) select TheDate, WetonanDay, WeekDayNumber, WeekDayName, MarketDayNumber, MarketDayName, GregorianName from vwWetonanDays GO

Now there are two most common Selects on this table. First I would search for a specific Date to get the Wetonan Day. Or I would search for a specific Wetonan Day in a given Date Range.

So 2 indexes would be useful here:

create unique index IX_TheDate
on WetonanDays(TheDate)
include(WetonanDay)
GO

create index IX_WetonanDay
on WetonanDays(WetonanDay)
include(TheDate)
GO

The first IX_TheDate index can be unique since there are no duplicate days. That's not the case with the IX_WetonanDay.

For both I included the other column that was needed for the searches I had in mind, preventing the need to read the actual pages since all the information is in the index already. Try it to find the WetonanDay for your Birthdate and you will see it is lightning fast!

That's it for today!

Coming up with your own cases and try to build a Query for that is always a good thing to improve your Query skills. Especially if that case contains something you have never done before or some Function you rarely use.

And when you tried this one on your own machine, try to find the WetonanDay for your Birthday. If you search on that with Google or ask your favorite AI tool about the meaning of it. I sure did!

That is it for today. Rahayu Sagung Dumadi!

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!