Unexpected behavior of DATE_PART_MILLIS function

SQL++
1

Hi there.

I’m trying to build a report over weeks and here is behavior of one of the date functions:

select DATE_PART_MILLIS(MILLIS("2016-01-04"), "week") as week

Actually responds with

[
  {
    "week": 1
  }
]

while my expectation would be “2”, because 4th of January is Monday, while year begins (1st of January) on Friday.

Is this expected and can I get proper week number? Can I set up that week is starting on Monday?

CB version: 4.5.0-2601 Enterprise Edition (build-2601) under Ubuntu 14 on EC2.

2

I think weeks start on Sunday. You may be able to add 1 day using DATE_ADD to switch to Monday. @isha

SELECT DATE_PART_STR( DATE_ADD_STR( my_date, 1, "day" ), "week" ) AS week

3

Thanks for your answer, but still it looks like week is not starting on Sunday on my CB instance.

select DATE_PART_MILLIS(MILLIS(“2016-01-01”), “week”) as week – FRIDAY
result “1”
select DATE_PART_MILLIS(MILLIS(“2016-01-04”), “week”) as week – MONDAY
result “1”

Same problem for 2015 year, if you do

select DATE_PART_MILLIS(MILLIS(“2015-01-01”), “week”) as week – THURSDAY
result “1”
select DATE_PART_MILLIS(MILLIS(“2015-01-05”), “week”) as week – MONDAY
result “1”

My assumption is that “week” value is calculated starting 1 January of the year, but not starting Sunday.

4

Ok, use ISO week.

SELECT DATE_PART_STR( DATE_ADD_STR( my_date, 1, "day" ), "iso_week" ) AS week

5

Omg, that’s embarrassing, I didn’t noticed that in manual here
http://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/datefun.html

Thanks a lot!

1 Like