After an evaluation, GNOME has moved from Bugzilla to GitLab. Learn more about GitLab.
No new issues can be reported in GNOME Bugzilla anymore.
To report an issue in a GNOME project, go to GNOME GitLab.
Do not go to GNOME Gitlab for: Bluefish, Doxygen, GnuCash, GStreamer, java-gnome, LDTP, NetworkManager, Tomboy.
Bug 623669 - UNIX2DATE issue
UNIX2DATE issue
Status: RESOLVED FIXED
Product: Gnumeric
Classification: Applications
Component: General
git master
Other Linux
: Normal normal
: ---
Assigned To: Jody Goldberg
Jody Goldberg
Depends on:
Blocks:
 
 
Reported: 2010-07-06 11:01 UTC by tokul
Modified: 2010-07-07 06:00 UTC
See Also:
GNOME target: ---
GNOME version: ---


Attachments
weekday() function tests (2.12 KB, application/x-gnumeric)
2010-07-06 11:01 UTC, tokul
Details

Description tokul 2010-07-06 11:01:48 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.
Comment 1 Morten Welinder 2010-07-06 13:40:47 UTC
Everything but UNIX2DATE fixed.

(For reference, the WEEKNUM issue is for line 1170, not 1111.  On line 1111,
"ISO week number" is correct.)
Comment 2 Andreas J. Guelzow 2010-07-06 14:20:03 UTC
UNIX2DATE also has a grammar issue. It should be:
"UNIX timestamps are numbers of ..."
or
"A UNIX timestamp is the number of ..."
Comment 3 Andreas J. Guelzow 2010-07-06 20:45:31 UTC
I have fixed the UNIX2DATE description.

I do not understand the difference between your result #1 and result #2.
Comment 4 tokul 2010-07-07 05:14:12 UTC
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.
Comment 5 tokul 2010-07-07 05:17:40 UTC
Unix timestamp counter starts at Midnight 1-Jan-1970 only in some timezones.
Comment 6 Andreas J. Guelzow 2010-07-07 06:00:34 UTC
Unix timestamps should always start at Midnight 1-Jan-1970 GMT. So the corresponding time in Gnumeric will depend on the current time zone.
Comment 7 Andreas J. Guelzow 2010-07-07 06:00:45 UTC
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.