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
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.
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).
The bug is still evident with master commit 1cf0999 pulled around 2013-07-28 0208 UTC. This includes commit cab9b82 fixing fdo#67186.
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...
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.
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.
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.
(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.