FYI to all interested.
lp:~jaypipes/drizzle/temporal contains the patches for this proposal,
and will be merged shortly.
Removes the WEEK(), WEEKOFYEAR(), YEARWEEK() functions, the
default_week_format variable and week_mode calculations, and corrects
invalid datetime formats for the EXTRACT() function.
To simulate ISO8601:1988 week number, simply do:
SELECT EXTRACT(WEEK FROM date_fld);
To simulate other week numbering:
/* Returns Sunday-first week number as an integer */
SELECT DATE_FORMAT(date_fld, "%U")+0;
/* Returns Monday-first week number as an integer */
SELECT DATE_FORMAT(date_fld, "%W")+0;
It would be trivial for anyone to add WEEK() back in as a UDF now.
Cheers,
Jay
Scott Bennett wrote:
sounds good. definitely sounds like a custom feature/plug-in rather than
a core functionality
Scott Bennett
______________________________________________________________
Please consider the environment, before printing this email.
On Wed, Feb 4, 2009 at 10:32 AM, Gerard Vignes <[email protected]
<mailto:[email protected]>> wrote:
i would like to see a database server that forces all application
logic to be implemented in the application itself: stripped down to
the barest functionality; i spent years building application logic
into sqlserver sprocs/udfs, but i finally saw the light and stopped
doing that;
i'm secretly hoping that drizzle turns out to be something like a
daemonized version of sqlite: bigger, faster, and dumber; if the
database were like a machine architecture and the orm were like a
complier, then i would want to see a risc solution;
-g
On Tue, Feb 3, 2009 at 2:34 PM, Monty Taylor <[email protected]
<mailto:[email protected]>> wrote:
> Jay Pipes wrote:
>> Hi all again,
>>
>> I would like to propose the removal of the WEEK() and WEEKOFYEAR()
>> functions from Drizzle and the corresponding default_week_format
session
>> variable, sometimes called the "week mode".
>
> Sounds fine to me. If someone really wants week() or weekofyear()
they
> can write them as UDFs.
>
>> Details of these things in MySQL can be found here:
>>
>>
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_week
>>
>>
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_weekofyear
>>
>>
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_default_week_format
>>
>>
>> Why am I proposing this? A number of reasons:
>>
>> 1) Simplify the date functions
>> 2) Standardize to format specifiers available in the C std
library for
>> strftime()
>> 3) Removal of custom code that duplicates the strftime() and
mktime()
>> functions in cstdlib (about 1000 lines of code).
>>
>> Will there be any loss of functionality?
>>
>> No. At least not any functionality that makes sense. :)
>>
>> The DATE_FORMAT() function has specifiers which enable getting
the week
>> number in three formats which correspond to the strftime() format
>> specifiers:
>>
>> %U returns the week number 00-53 where week 1 is the first week
>> containing a Sunday. Days in a week preceding the first Sunday
are in
>> week 0.
>>
>> %W returns the week number 00-53 where week 1 is the first week
>> containing a Monday. Days in a week preceding the first Monday
are in
>> week 0.
>>
>> %V returns 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. This format is the format specified by ISO8601:1988.
>>
>> Currently, the WEEK() function is, IMHO, overly complex and mostly
>> redundant with DATE_FORMAT() given one of the specifiers above.
It uses
>> a week_mode second argument, which influences the return of the
function
>> per the following table:
>>
>> Mode First day of week Range Week 1 is the first week...
>> ------ ------------------- ------- -------------------------------
>> 0 Sunday 0-53 with a Sunday in this year
>> 1 Monday 0-53 with more than 3 days this year
>> 2 Sunday 1-53 with a Sunday in this year
>> 3 Monday 1-53 with more than 3 days this year
>> 4 Sunday 0-53 with more than 3 days this year
>> 5 Monday 0-53 with a Monday in this year
>> 6 Sunday 1-53 with more than 3 days this year
>> 7 Monday 1-53 with a Monday in this year
>>
>> If we removed the WEEK() function and week_mode variable
entirely, the
>> only supported behaviours would be modes 0, 3, and 5. All other
modes
>> correspond to behaviour that is contrary to any standard.
>>
>> ISO8601:1988 dictates that the first week is the one containing more
>> than 3 days in a year, and that Monday is the start of the week
and week
>> numbers are numbered 1-53. Therefore modes 1, 4, and 6 produce
>> behaviour that is actually dangerous: it pretends to be ISO8601, but
>> isn't correct.
>>
>> Therefore, I propose the removal of WEEK() and week_mode.
>>
>> Here is how one might produce the identical result to WEEK() for
modes
>> 0, 3, and 5 using DATE_FORMAT() properly:
>>
>> # "mode 0"
>> SELECT DATE_FORMAT("2008-01-01", "%U")+0;
>> # "mode 3"
>> SELECT DATE_FORMAT("2008-01-01", "%V")+0;
>> # "mode 5"
>> SELECT DATE_FORMAT("2008-01-01", "%W")+0;
>>
>> Please vote for whether my proposal has merit.
>>
>> Thanks,
>>
>> Jay
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~drizzle-discuss
<https://launchpad.net/%7Edrizzle-discuss>
>> Post to : [email protected]
<mailto:[email protected]>
>> Unsubscribe : https://launchpad.net/~drizzle-discuss
<https://launchpad.net/%7Edrizzle-discuss>
>> More help : https://help.launchpad.net/ListHelp
>>
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~drizzle-discuss
<https://launchpad.net/%7Edrizzle-discuss>
> Post to : [email protected]
<mailto:[email protected]>
> Unsubscribe : https://launchpad.net/~drizzle-discuss
<https://launchpad.net/%7Edrizzle-discuss>
> More help : https://help.launchpad.net/ListHelp
>
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
<https://launchpad.net/%7Edrizzle-discuss>
Post to : [email protected]
<mailto:[email protected]>
Unsubscribe : https://launchpad.net/~drizzle-discuss
<https://launchpad.net/%7Edrizzle-discuss>
More help : https://help.launchpad.net/ListHelp
------------------------------------------------------------------------
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
