• Do not register here on develop.twiki.org, login with your twiki.org account.
• Use View topic Item7700 for generic doc work for TWiki-6.0.2. Use View topic Item7703 for doc work on extensions that are not part of a release. More... Close
• Anything you create or change in standard webs (Main, TWiki, Sandbox etc) will be automatically reverted on every SVN update.
Does this site look broken?. Use the LitterTray web for test cases.

Item6526: Add ISO 8601 week number to FORMATTIME

Item Form Data

AppliesTo: Component: Priority: CurrentState: WaitingFor: TargetRelease ReleasedIn
Extension SpreadSheetPlugin Enhancement Closed   patch 5.0.1

Edit Form Data

Summary:
Reported By:
Codebase:
Applies To:
Component:
Priority:
Current State:
Waiting For:
Target Release:
Released In:
 

Detail

Originally proposed by TWiki:Main.PeterPayne on 2007-10-22 in TWiki:Plugins.SpreadSheetPluginDev.

Original spec was to add a new ISODATE function. I think it is better to add new tokens to the existing FORMATGMTIME and FORMATTIME function. Because there are many variants I think it is better to add a $isoweek(format) instead of many new tokens:

  • $isoweek : 1 or 2 digits ISO 8601 week number, such as 53 for 2010-01-03
  • $isoweek($yearW$wk$day) : ISO 8601 week number, such as 2009W537 for 2010-01-03
  • $isoweek($year-W$wk-$day) : ISO 8601 week number, such as 2009-W53-7 for 2010-01-03
  • $isoweek($year-W$wk) : ISO 8601 week number, such as 2009-W53 for 2010-01-03
  • $isoweek($year) : ISO 8601 week number, such as 2009 for 2010-01-03
  • $isoweek($wk) : ISO 8601 week number, such as 53 for 2010-01-03
  • $isoweek($day) : ISO 8601 week number, such as 7 for 2010-01-03

Alternatively we could define this spec:

  • $isoweek : 1 or 2 digit ISO 8601 week number, such as 53 for 2010-01-03
  • $isoweek-yearWwkd : ISO 8601 week number, such as 2009W537 for 2010-01-03
  • $isoweek-year-Wwk-d : ISO 8601 week number, such as 2009-W53-7 for 2010-01-03
  • $isoweek-year-Wwk : ISO 8601 week number, such as 2009-W53 for 2010-01-03
  • $isoweek-year : ISO 8601 week number, such as 2009 for 2010-01-03
  • $isoweek-wk : ISO 8601 week number, 2 digits, such as 53 for 2010-01-03
  • $isoweek-d : ISO 8601 week number, such as 7 for 2010-01-03

Which spec is better?

Updated doc:

FORMATTIME( serial, text ) -- convert a serialized date into a date string

  • The following variables in text are expanded:
    • $second - seconds, 00..59
    • $minute - minutes, 00..59
    • $hour - hours, 00..23
    • $day - day of month, 01..31
    • $month - month, 01..12
    • $mon - month in text format, Jan..Dec
    • $year - 4 digit year, 1999
    • $ye - 2 digit year, 99
    • $wd - day number of the week, 1 for Sunday, 2 for Monday, etc
    • $wday - day of the week, Sun..Sat
    • $weekday - day of the week, Sunday..Saturday
    • $yearday - day of the year, 1..365, or 1..366 in leap years
    • $isoweek - ISO 8601 week number, one or two digits, 1..53
    • $isoweek(format) - formatted ISO 8601 week number, format may contain year for ISO week-numbering year, wk for ISO week number, day for ISO weekday number, iso for ISO year-week number. Examples:
      • $isoweek(iso) - full year-week ISO week number, such as 2009-W53 for 2010-01-03
      • $isoweek(yearWwkday) - full year-week-day ISO week number, such as 2009W537 for 2010-01-03
      • $isoweek(year-Wwk-day) - full year-week-day ISO week number, such as 2009-W53-7 for 2010-01-03
      • $isoweek(year-Wwk) - year-week ISO 8601 week number, such as 2009-W53 for 2010-01-03
      • $isoweek(year) - year of ISO 8601 week number, such as 2009 for 2010-01-03
      • $isoweek(wk) - 2 digit ISO 8601 week number, such as 53 for 2010-01-03
      • $isoweek(day) - day of ISO 8601 week number, starting with 1 for Monday, such as 7 for 2010-01-03
  • Date is assumed to be server time; add GMT to text to indicate Greenwich time zone.
  • Syntax: $FORMATTIME( serial, text )
  • Example: %CALC{"$FORMATTIME(0, $year/$month/$day GMT)"}% returns 1970/01/01 GMT
  • Related: $FORMATGMTIME(), $TIME(), $FORMATTIMEDIFF(), $TIMEADD(), $TIMEDIFF(), $TODAY()

Doc at http://en.wikipedia.org/wiki/ISO_8601#Week_dates :

Format:
  • YYYY-Www or YYYYWww
  • YYYY-Www-D or YYYYWwwD

Week date representations are in the format as shown above. [YYYY] indicates the ISO week-numbering year which is slightly different to the calendar year (see below). [Www] is the week number prefixed by the letter 'W', from W01 through W53. [D] is the weekday number, from 1 through 7, beginning with Monday and ending with Sunday. This form is popular in the manufacturing industries.

There are mutually equivalent descriptions of week 01:

  • the week with the year's first Thursday in it (the formal ISO definition),
  • the week with 4 January in it,
  • the first week with the majority (four or more) of its days in the starting year, and
  • the week starting with the Monday in the period 29 December 4 January.

If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week 01. If 1 January is on a Friday, Saturday or Sunday, it is in week 52 or 53 of the previous year (there is no week 00). 28 December is always in the last week of its year.

The week number can be described by counting the Thursdays: week 12 contains the 12th Thursday of the year.

The ISO week-numbering year starts at the first day (Monday) of week 01 and ends at the Sunday before the new ISO year (hence without overlap or gap). It consists of 52 or 53 full weeks. The ISO week-numbering year number deviates from the number of the calendar year (Gregorian year) on a Friday, Saturday, and Sunday, or a Saturday and Sunday, or just a Sunday, at the start of the calendar year (which are at the end of the previous ISO week-numbering year) and a Monday, Tuesday and Wednesday, or a Monday and Tuesday, or just a Monday, at the end of the calendar year (which are in week 01 of the next ISO week-numbering year). For Thursdays, the ISO week-numbering year number is always equal to the calendar year number.

Examples:

  • 2008-12-29 is written "2009-W01-1"
  • 2010-01-03 is written "2009-W53-7"

For an overview of week numbering systems see week number. The US system has weeks from Sunday through Saturday, and partial weeks at the beginning and the end of the year. An advantage is that no separate year numbering like the ISO week-numbering year is needed, while correspondence of lexicographical order and chronological order is preserved.

-- TWiki:Main/PeterThoeny - 2010-07-19

This is now implemented in SVN trunk and 5.0 branch.

Last minute spec change from $isoweek(wk) to $isoweek($wk).

-- PeterThoeny - 2010-08-27

ItemTemplate
Summary Add ISO 8601 week number to FORMATTIME
ReportedBy TWiki:Main.PeterThoeny
Codebase 5.0.0, ~twiki4
SVN Range TWiki-5.0.0, Tue, 06 Jul 2010, build 19208
AppliesTo Extension
Component SpreadSheetPlugin
Priority Enhancement
CurrentState Closed
WaitingFor

Checkins TWikirev:19374 TWikirev:19375 TWikirev:19376 TWikirev:19377 TWikirev:19378 TWikirev:19379 TWikirev:19380 TWikirev:19381
TargetRelease patch
ReleasedIn 5.0.1
Edit | Attach | Watch | Print version | History: r17 < r16 < r15 < r14 < r13 | Backlinks | Raw View | Raw edit | More topic actions
Topic revision: r17 - 2010-10-12 - PeterThoeny
 
This site is powered by the TWiki collaboration platform Powered by PerlCopyright © 2008-2017 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback