Feature request

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Feature request

Jean-Christophe Deschamps-3
Dear list,

I often have to use SQLite strftime() to compute a week number but the
only proposed format '%W' causes problems.
Not only that north-american week number has a varying range [00..52]
or [01..53] but some years (e.g. 2012, 2040) yield a result in
[00..53], making those years 54 weeks.

This is a major inconvenience for a number of applications, since
deriving the ISO week number from an ISO date as part of a [sub]query
or condition is a real pain and slows down things significantly.

Would it be possible to add the '%V' format (ISO week number in
[01..53]) in some future release?


--
Jean-Christophe Deschamps
2891 route de Pouillon
40180 Heugas
France
06 15 10 19 29  

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request

Simon Slavin-3

On 16 Jan 2017, at 7:53am, Jean-Christophe Deschamps <[hidden email]> wrote:

> Would it be possible to add the '%V' format (ISO week number in [01..53]) in some future release?

For those playing along at home, the EU week starts on a Monday, with week 1 being the one which contains the first Thursday of the year.  The USA week starts on a Sunday, with week 1 being the one which contains the first Saturday of the year.

I cannot speak for the development team, but I wanted to check your definition.  Are you asking for the ISO 8601 week number as specified on this page:

<https://en.wikipedia.org/wiki/ISO_week_date>

Do you want them always to be two digits long, as specified in the standard ?
Do you want them preceded by the mandatory 'W' or would you expect to have the programmer add that themself ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request

Hick Gunter
Please be aware that %V implies %G/%g (four and two digit ISO Year number), which differs from %Y/%y on the "spillover days" that belong to the first/last week of the "other" year.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Simon Slavin
Gesendet: Montag, 16. Jänner 2017 11:44
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] Feature request


On 16 Jan 2017, at 7:53am, Jean-Christophe Deschamps <[hidden email]> wrote:

> Would it be possible to add the '%V' format (ISO week number in [01..53]) in some future release?

For those playing along at home, the EU week starts on a Monday, with week 1 being the one which contains the first Thursday of the year.  The USA week starts on a Sunday, with week 1 being the one which contains the first Saturday of the year.

I cannot speak for the development team, but I wanted to check your definition.  Are you asking for the ISO 8601 week number as specified on this page:

<https://en.wikipedia.org/wiki/ISO_week_date>

Do you want them always to be two digits long, as specified in the standard ?
Do you want them preceded by the mandatory 'W' or would you expect to have the programmer add that themself ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request

Simon Slavin-3

On 16 Jan 2017, at 12:17pm, Hick Gunter <[hidden email]> wrote:

> Please be aware that %V implies %G/%g (four and two digit ISO Year number), which differs from %Y/%y on the "spillover days" that belong to the first/last week of the "other" year.

Can you tell me where your %G and %g definitions are coming from ?  I thought %g was for printing the shortest representation of a number.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request

Stephen Chrzanowski
Straight up, I've never had to concern myself with the week number of a
year.  I'm aware of it, but, with it a moving number year to year, I've
never relied on it, or even had the requirement/desire to output it as a
result, except maybe for 'fun'.

The SQLite.org page references that strftime goes against the standard C
library and contains a link to
http://pubs.opengroup.org/onlinepubs/007908799/xsh/strftime.html and
mentions only a subset of substitutions are available (Side question: Why
only a subset?)

I don't see %G or %g as a modifier.  The last time I wrote anything in C
was in in my college days, so I'm not entirely familiar with strftime.
Could %G/%g be a sprintf thing, and not a strftime thing?

From the above link:

%V is replaced by the week number of the year (Monday as the first day of
the week) as a decimal number [01,53]. If the week containing 1 January has
four or more days in the new year, then it is considered week 1. Otherwise,
it is the last week of the previous year, and the next week is week 1. %W
is replaced by the week number of the year (Monday as the first day of the
week) as a decimal number [00,53]. All days in a new year preceding the
first Monday are considered to be in week 0.

So with my side question, the OPs concerns about a 54 week year, and
pending a serious concern about allowing things to strftime, I'm not sure
why %V couldn't be added?

On Mon, Jan 16, 2017 at 7:29 AM, Simon Slavin <[hidden email]> wrote:

>
> On 16 Jan 2017, at 12:17pm, Hick Gunter <[hidden email]> wrote:
>
> > Please be aware that %V implies %G/%g (four and two digit ISO Year
> number), which differs from %Y/%y on the "spillover days" that belong to
> the first/last week of the "other" year.
>
> Can you tell me where your %G and %g definitions are coming from ?  I
> thought %g was for printing the shortest representation of a number.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request

Hick Gunter
In reply to this post by Simon Slavin-3
http://man7.org/linux/man-pages/man3/strftime.3.html

"ISO 8601 week dates
       %G, %g, and %V yield values calculated from the week-based year
       defined by the ISO 8601 standard.  In this system, weeks start on a
       Monday, and are numbered from 01, for the first week, up to 52 or 53,
       for the last week.  Week 1 is the first week where four or more days
       fall within the new year (or, synonymously, week 01 is: the first
       week of the year that contains a Thursday; or, the week that has 4
       January in it).  When three of fewer days of the first calendar week
       of the new year fall within that year, then the ISO 8601 week-based
       system counts those days as part of week 53 of the preceding year.
       For example, 1 January 2010 is a Friday, meaning that just three days
       of that calendar week fall in 2010.  Thus, the ISO 8601 week-based
       system considers these days to be part of week 53 (%V) of the year
       2009 (%G); week 01 of ISO 8601 year 2010 starts on Monday, 4 January
       2010."

%g as "shortest representation of a float" is from (s)printf format specifiers.


-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Simon Slavin
Gesendet: Montag, 16. Jänner 2017 13:30
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] Feature request


On 16 Jan 2017, at 12:17pm, Hick Gunter <[hidden email]> wrote:

> Please be aware that %V implies %G/%g (four and two digit ISO Year number), which differs from %Y/%y on the "spillover days" that belong to the first/last week of the "other" year.

Can you tell me where your %G and %g definitions are coming from ?  I thought %g was for printing the shortest representation of a number.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request

Simon Slavin-3
In reply to this post by Stephen Chrzanowski

On 16 Jan 2017, at 1:10pm, Stephen Chrzanowski <[hidden email]> wrote:

> From the above link:
>
> %V is replaced by the week number of the year (Monday as the first day of
> the week) as a decimal number [01,53]. If the week containing 1 January has
> four or more days in the new year, then it is considered week 1. Otherwise,
> it is the last week of the previous year, and the next week is week 1. %W
> is replaced by the week number of the year (Monday as the first day of the
> week) as a decimal number [00,53]. All days in a new year preceding the
> first Monday are considered to be in week 0.

That almost fits the definition of an ISO week number, not the one for a USA week number.  So it is what the OP was asking for.

> So with my side question, the OPs concerns about a 54 week year, and
> pending a serious concern about allowing things to strftime, I'm not sure
> why %V couldn't be added?

I think it would be easy to add but I’m not part of the dev group and don’t really know if this is the case.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request

Richard Hipp-3
On 1/16/17, Simon Slavin <[hidden email]> wrote:
>
>
> I think it would be easy to add but I’m not part of the dev group and don’t
> really know if this is the case.
>

The implementation is here:
https://www.sqlite.org/src/artifact/dc3f1391d9297f8c?ln=983-1133

Who can send me a patch?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users