Bug 65354 - REPORTBUILDER: Incorrect time zone being displayed in date field
Summary: REPORTBUILDER: Incorrect time zone being displayed in date field
Status: RESOLVED INVALID
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.0.3.3 release
Hardware: x86-64 (AMD64) macOS (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: BSA target:4.2.0
Keywords:
Depends on:
Blocks:
 
Reported: 2013-06-04 14:24 UTC by J Liu
Modified: 2013-08-14 06:51 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Bug reproduction example (7.88 KB, application/vnd.oasis.opendocument.database)
2013-06-04 14:24 UTC, J Liu
Details

Note You need to log in before you can comment on or make changes to this bug.
Description J Liu 2013-06-04 14:24:44 UTC
Created attachment 80292 [details]
Bug reproduction example

Problem description: In reports, when TIMEVALUE(NOW()) is used in the report builder, the value that gets displayed in a report differs depending on whether a string gets appended to it. In particular, daylight savings time seems to be getting ignored when TIMEVALUE(NOW()) is concatenated with a string.

Steps to reproduce:
1. Open attachment 'reportsTimeZoneBug.odb'.
2. In the left pane, click "Reports".
3. In the lower-right pane, right-click and choose "Edit".
4. Move the report builder to the side. Now, got back to the lower-right pane, and double-click the report.


Current behavior: When TIMEVALUE(NOW()) is used alone, or when NOW() is concatenated to a string, the correct time zone (including daylight savings) is used. However, when TIMEVALUE(NOW()) is concatenated to a string, an incorrect time zone is being displayed.

Expected behavior: NOW() with string concatenation and TIMEVALUE(NOW()) with string concatenation should both be displaying the same time zone.

Additional information: I am in a locale that has daylight savings time (United States Eastern Time Zone), so testing the file on a computer in a locale that does not have daylight savings may cause the bug to not be visible.
              
Operating System: Mac OS X
Version: 4.0.3.3 release
Comment 1 Terrence Enger 2013-07-05 15:34:04 UTC
Setting bug status NEW.

In the reporter's timezone when daylight savings is in effect, with
master commit a08f579 fetched 2013-06-28 18:30 UTC, I see the result
he described.
Comment 2 Lionel Elie Mamane 2013-08-05 03:32:19 UTC
This might have been fixed with the fix of bug 67186; I'd be grateful if someone could retest with recent master or 4.1.1 or 4.0.5 (rc is fine for the test).
Comment 3 Terrence Enger 2013-08-05 12:52:51 UTC
The bug is still evident with master commit 1cf0999 pulled around
2013-07-28 0208 UTC.  This includes commit cab9b82 fixing fdo#67186.
Comment 4 Lionel Elie Mamane 2013-08-06 09:12:14 UTC
So, in my timezone (Europe/Luxembourg), which is now in CEST and has CET as winter time, the time is displayed correctly, but it says "CET" instead of CEST.

From what I gather, TIMEVALUE(NOW()) is not supposed to work *at* *all*; it should give an error message:

 - NOW() returns a *number* (a "serial" date)
 - TIMEVALUE() takes a *string* (that encodes a date)

Anyway, that is most probably a pentaho/jfreereport bug. I'm looking into it...
Comment 5 Lionel Elie Mamane 2013-08-06 10:47:30 UTC
After some more analysis / more thought, I rest on "it is not supposed to work at all". It cannot work. Use
 TEXT(NOW(); "HH:mm:ss z")
instead of
 TIMEVALUE(NOW())

Here's what happens:
 NOW() returns a Date+Time, with timezone information and all.
So when you do the formula
 "blah blah " & NOW()
you get the default formatting, which is the Java "FULL", including timezone. Which is determined correctly because it has a date to rely one, so it can apply daylight time saving rules.

When you do
 TIMEVALUE(NOW())
you strip the date away. When you then append a string to that, again default formatting, that is Java "FULL". Which slaps the winter timezone on it, probably because "no time" is understood as "1 January 1900" or some such.

Now, you'll (maybe?) tell me that slapping a timezone on a time-without-date does not make sense, precisely because there is ambiguity between winter time and summer time. So the default formatting of a time should not include any timezone. And I do agree with that.
Comment 6 Commit Notification 2013-08-07 08:16:49 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=def99ae60119658d0b24667b08068162d2454694

fdo#65354 don't slap a timezone on time values



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 7 J Liu 2013-08-13 18:11:58 UTC
OK, I just tested, and can confirm that Lionel's suggested solution of using

TEXT(NOW(); "HH:mm:ss z")

works as expected.

However, I don't necessarily think that "slapping a timezone on a time-without-date does not make sense, precisely because there is ambiguity between winter time and summer time. So the default formatting of a time should not include any timezone." The timezone is an indicator of locale on the planet, right? Since the time itself (even if it is not associated with a specific date) is also location-dependent,  I believe that having the timezone shown on just a time should still be a perfectly valid concept.

So if I say that something occurred at 17:34 EDT, it means that you know what time of day it occurred (in the afternoon), and you know approximately where it occurred (probably somewhere near the longitude that lines up with New York City, since EDT is Eastern Daylight Time). The EDT can even tell you an approximate date when it occurred (the Daylight Time means that it is indeed summer time), even though you have no idea what is the exact date I am talking about.
Comment 8 Lionel Elie Mamane 2013-08-14 06:51:45 UTC
(In reply to comment #7)

> However, I don't necessarily think that "slapping a timezone on a
> time-without-date does not make sense, precisely because there is ambiguity
> between winter time and summer time. So the default formatting of a time
> should not include any timezone." The timezone is an indicator of locale on
> the planet, right? Since the time itself (even if it is not associated with
> a specific date) is also location-dependent,  I believe that having the
> timezone shown on just a time should still be a perfectly valid concept.

1) A timezone is not only a location. The same place in the world has
   a different timezone depending on the date.

2) If all you have is a time, and no locale, then which locale would
   you want to slap on that time? It is essentially undefined, it
   could be *any*. The choice of libformula was to put the *local*
   timezone on it, which I find dubious in the first place. And is
   doomed to failure because there are two local timezones: the
   summer one and the winter one.

> So if I say that something occurred at 17:34 EDT,

Yes, but that's only if you know in which timezone it happened. If
all you have is "17:34", as opposed to "17:34 EDT", then slapping
any of EDT, EST, CEST, CET, ... on it makes no sense, because it
is unknown which one it is.

TIMEVALUE() returns (or at least is supposed to return) just a time,
without any timezone information.