1

Is there any way to tell SlamData that a week starts on Monday (as it does in Germany)?

Want to GROUP BY DATE_PART("week", c.createdAt) which is always wrong as SlamData's week start a day earlier than ours.

In some Database systems you can "SET DATEFIRST 1" to solve this but not here.

Ankur Alankar Biswal
  • 1,184
  • 1
  • 12
  • 23
Frank Geßner
  • 11
  • 1

2 Answers2

0

You can use a SQL CASE expression to achieve the effect you desire:

GROUP BY 
  (CASE DATE_PART("week", c.createdAt)
    WHEN 1 THEN 7
    WHEN 2 THEN 1
    WHEN 3 THEN 2
    WHEN 4 THEN 3
    WHEN 5 THEN 4
    WHEN 6 THEN 5
    WHEN 7 THEN 6
  END)

This will shift Sunday to the last day of the week.

John A. De Goes
  • 568
  • 3
  • 7
0

It seems that SQL's definition of "week of year" is clashing with MongoDB's.

According to the PostgreSQL docs:

By definition, ISO weeks start on Mondays and the first week of a year contains January 4 of that year.

Whereas MongoDB has this:

Weeks begin on Sundays, and week 1 begins with the first Sunday of the year... This behavior is the same as the “%U” operator to the strftime standard library function.

Quasar (the query engine behind SlamData) is using the MongoDB operator directly in this case, so that's the behavior you'll see.

I suspect it's possible to get one from the other, which is what Quasar should be doing, and I encourage you to write a github issue reporting this as a bug: https://github.com/quasar-analytics/quasar/issues.

It should also be possible to express the same fix in SQL as a workaround, but it's not likely to be pretty. If I end up fixing the bug in Quasar, I'll update this answer with the formula I come up with.

Moss Prescott
  • 104
  • 3