Handle week day start in Week display/argument [#2625054]

Follow-up to #2624464: Create Year Week date argument

Handle week day start in Week display

In \Drupal\views\Plugin\views\query\Sql::getDateFormat which changes the date argument into a string for the datebase 'W' is converted to '%v' which is for week starting on Monday in MySql.

We would need to check current week start setting and switch to '%V'(capitalized) for weeks starting on Sunday.

See https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#fun...

I am not sure about other supported databases.

Comments

Comment #1

29 November 2015 at 00:49

tedbow created an issue. See original summary.

Comment #2

tedbow

he/him

English

Ithaca, NY, USA

CreditAttribution: tedbow at Acquia commented 29 November 2015 at 00:50

Title:

Create Year Week date argument

» Handle week day start in Week display/argument

Comment #3

tedbow

he/him

English

Ithaca, NY, USA

CreditAttribution: tedbow at Acquia commented 4 December 2015 at 16:37

Status:

Active

» Postponed

This is really a core issue.

\Drupal\views\Plugin\views\query\Sql::getDateFormat

Need to have a if statement that checks $this->config('system.date')->get('first_day')
and then switch between '%v' or '%V' accordingly

I will file a core issue. This might need to be pulled into calendar_datetime

Comment #4

Anonymous (not verified) CreditAttribution: Anonymous at XIO commented 13 December 2015 at 19:55

Did you file a core issue? I can't seem to find it.

Comment #5

tedbow

he/him

English

Ithaca, NY, USA

CreditAttribution: tedbow at Acquia commented 13 December 2015 at 21:03

@pjonckiere no I haven't filed a core issue yet. Let me know if you do. I will post here I get chance to post a core issue.

Comment #6

Anonymous (not verified) CreditAttribution: Anonymous at XIO commented 14 December 2015 at 21:04

Fwiw, both pgsql and sqlite don't define a format pattern for weeks starting on Sunday. Only MySQL does that.
Refs:
- https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#fun...
- http://www.postgresql.org/docs/8.2/static/functions-formatting.html
- https://www.sqlite.org/lang_datefunc.html

If this is a display issue (rather than a query issue), could we possibly use DateHelper::weekDaysOrdered()?

Comment #7

tedbow

he/him

English

Ithaca, NY, USA

CreditAttribution: tedbow at Acquia commented 15 December 2015 at 13:51

@pjonckiere thanks for doing the research into Postgres and SQLite

If this is a display issue (rather than a query issue), could we possibly use DateHelper::weekDaysOrdered()?

This is actually an issue with the way the actual queries are built so it will affect which dates are match in the sql statement.

It is weird because as far as I can tell the region setting in core that sets which weekday the week starts are isn't actually used for anything in core. This module is using it but it would seem logical for Views in core to use this setting but it is not.

Since calendar is providing it's own argument for YearWeek then I guess we could handle this conversion for MySQL ourselves.

Comment #8

Anonymous (not verified) CreditAttribution: Anonymous at XIO commented 15 December 2015 at 21:29

This is actually an issue with the way the actual queries are built so it will affect which dates are match in the sql statement.

I feared that much.

It is weird because as far as I can tell the region setting in core that sets which weekday the week starts are isn't actually used for anything in core. This module is using it but it would seem logical for Views in core to use this setting but it is not.

I came to the same conclusion. It's odd. However, it could be by design since it implements the ISO standard. Not sure about that though.

So I was thinking that this could be a regression, but it's not. Both the D7 views and date_api contrib modules define these mapping handlers, but neither of them defines '%V'. It might be a good idea to find out if this worked in D7, and if yes, find out how.