GNOME Bugzilla – Bug 623669
UNIX2DATE issue
Last modified: 2010-07-07 06:00:45 UTC
Created attachment 165345 [details] weekday() function tests #../plugins/fn-date/functions.c:139 The UNIT2DATE function translates Unix timestamps into date serial values. Unix timestamps are number of seconds since Midnight 1-Jan-1900. test function call in Gnumeric =UNIX2DATE(0) Result 1 (the way unixtime is counted in real world) - Thu, 01 Jan 1970 00:00:00 GMT (reference - http://www.onlineconversion.com/unix_time.htm, http://en.wikipedia.org/w/index.php?title=Unix_time&oldid=370050636) Result 2 (the way gnumeric counts it) - 1970-01-01 03:00:00 (with locale timezone difference, EEST in my case, 25569 in serial date format) Result 3 (the way it is counted according to function description) - 1900-01-01 00:00:00 if function name from functions.c:139 is used, it creates undefined function error in Gnumeric 1.10.5. Help is 70 years off and uses invalid function name. I am not sure if description should warn users about difference in leap seconds. #../plugins/fn-date/functions.c:615-616 If @{method} is 2, then Monday is 1, Sunday is 2, etc. If @{method} is 3, then Monday is 0, Sunday is 1, etc. Wrong weekday order. Second in a row is not Sunday, but Tuesday. Sample gnumeric weekday() method tests are attached. #../plugins/fn-date/functions.c:1077 ISOYEAR calculates the year to go with week number according to the ISE 8601 standard. ISE -> ISO #../plugins/fn-date/functions.c:1111 WEEKNUM:ISO week number I suspect that ISO something is used only when @{method} is set to 150 Translation file is from http://l10n.gnome.org/. Checked if http://git.gnome.org/browse/gnumeric/tree/plugins/fn-date/functions.c shows same types.
Everything but UNIX2DATE fixed. (For reference, the WEEKNUM issue is for line 1170, not 1111. On line 1111, "ISO week number" is correct.)
UNIX2DATE also has a grammar issue. It should be: "UNIX timestamps are numbers of ..." or "A UNIX timestamp is the number of ..."
I have fixed the UNIX2DATE description. I do not understand the difference between your result #1 and result #2.
Gnumeric returns date and time in current timezone. unixtime is counted in UTC timezone. If gnumeric is started with 'TZ="GMT" gnumeric' command, UNIX2DATE(0) result is 1970-01-01 00:00:00. If gnumeric is started with 'TZ="Europe/Vilnius" gnumeric' command, result is 1970-01-01 03:00:00. If texts are fixed (1900 -> 1970, UNIT2DATE -> UNIX2DATE and whatever grammar changes you like), then this report can be closed. Difference in result1 and result2 is not that important.
Unix timestamp counter starts at Midnight 1-Jan-1970 only in some timezones.
Unix timestamps should always start at Midnight 1-Jan-1970 GMT. So the corresponding time in Gnumeric will depend on the current time zone.
This problem has been fixed in the development version. The fix will be available in the next major software release. Thank you for your bug report.