Bug Hunting Session
Bug 83116 - Cannot convert dates with fractional seconds >= 0.9995
Summary: Cannot convert dates with fractional seconds >= 0.9995
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.0 release
Hardware: x86 (IA32) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-08-26 22:56 UTC by ariel cornejo
Modified: 2017-05-22 13:26 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
sample data (2.32 KB, text/plain)
2014-08-26 22:56 UTC, ariel cornejo
Details
test results (17.27 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-10-11 14:46 UTC, ariel cornejo
Details

Note You need to log in before you can comment on or make changes to this bug.
Description ariel cornejo 2014-08-26 22:56:23 UTC
Created attachment 105310 [details]
sample data

Open the attached file with Calc; use Tab as a delimiter and choose "Date (YMD)" for either column. The values with at least 0.9995 fractional seconds will fail to be converted.
Comment 1 m.a.riosv 2014-08-27 00:30:20 UTC
I think the issue is that those values exceeded the maximum to manage as date/time values, thousandths of a second.
And to round 0,9995 implies a +1 second what doesn't seem properly to transform the text in a date/time value.
Preserving in the text only three decimals, conversion it's done properly.

I don't know if it can be considerer really a bug.
Comment 2 ariel cornejo 2014-08-27 01:37:14 UTC
I agree about the likely cause but I do see it as a bug or needful improvement; an user could argue that if "12:23:34.9994" is a legitimate time, so is "12:23:34.9996".

I think I can fix it; I'd either:
 - parse the whole "SS.0000" bit as a float respecting the regional settings,
 - add fractional_seconds/length(fractional_seconds) to the ongoing value, or
 - if the precision must be limited to 1 ms, allow 1 s to be carried over to the seconds part.

Any hints on the relevant bit of code? I'll look into setting up a development environment. Also, is there a spec that says that decimal digits beyond the third must be truncated or rounded in conversion? I thought dates were floating-point numbers.
Comment 3 ariel cornejo 2014-08-27 01:56:34 UTC
Oops, I forgot to mention that at work, this failed only when opening the text file directly or using 'Data/Text to Columns', but 'Edit/Paste Special' works OK. At home (also 4.3.0.4, but on 64-bit Ubuntu), only File/Open seems to fail. I'll double-check tomorrow.
Comment 4 Owen Genat (retired) 2014-10-11 13:20:34 UTC
(In reply to ariel cornejo from comment #0)
> Open the attached file with Calc; use Tab as a delimiter and choose "Date
> (YMD)" for either column. The values with at least 0.9995 fractional seconds
> will fail to be converted.

Unable to reproduce under GNU/Linux using v4.3.2.2. The initially imported values are displayed using a date-time format code of DD/MM/YY HH.MM however editing this to DD/MM/YYYY HH.MM.SS.0000000 reveals sub-second detail. Perhaps try highlighting the imported data and using the indicated format to see if it makes any difference?

Status set to NEEDINFO. Please set back to UNCONFIRMED once the requested information is provided.
Comment 5 ariel cornejo 2014-10-11 14:45:26 UTC
(In reply to Owen Genat from comment #4)
> Unable to reproduce under GNU/Linux using v4.3.2.2. The initially imported
> values are displayed using a date-time format code of DD/MM/YY HH.MM however
> editing this to DD/MM/YYYY HH.MM.SS.0000000 reveals sub-second detail.
> Perhaps try highlighting the imported data and using the indicated format to
> see if it makes any difference?

I just tried with version 4.4.0.0.alpha0 (24fb87501ef9d5aa715d572de7eb5efe49a0d9c3). Values in rows 4,14,15,25,26,36-40 are imported as text:

* the cells show e.g. "2014-08-26 15:01:52.999609000" and are left aligned
* TYPE() evaluates to 2
* the format can't be changed
* interestingly, arithmetic expressions like =F2+1/24 work on these unconverted cells. Apparently they're being converted automatically.

These are the results of three conversion methods:

File > Open > Column type = Date (YMD):
Paste > Column type = Text > Text to columns > Column type = Date (YMD):
Cells with fractional part < 0.9995 are imported as dates and the rest remain as text. 

Paste > Column type = YMD:
OK. TYPE() evaluates to 1 and the format can be changed.

> Status set to NEEDINFO. Please set back to UNCONFIRMED once the requested
> information is provided.

I'm attaching a spreadsheet with the results of all three cases.

Any idea on the relevant bits of source for conversion? I built from source specifically to help tackle this.
Comment 6 ariel cornejo 2014-10-11 14:46:03 UTC
Created attachment 107721 [details]
test results
Comment 7 Owen Genat (retired) 2014-10-12 04:58:54 UTC
(In reply to ariel cornejo from comment #5)
> I just tried with version 4.4.0.0.alpha0
> (24fb87501ef9d5aa715d572de7eb5efe49a0d9c3). Values in rows
> 4,14,15,25,26,36-40 are imported as text:

That is quite a strange result. I have no problem here using:

v4.1.6.2 Build ID: 40ff705089295be5be0aae9b15123f687c05b0a
v4.2.6.3 Build ID: 3fd416d4c6db7d3204c17ce57a1d70f6e531ee21
v4.3.2.2 Build ID: edfb5295ba211bd31ad47d0bad0118690f76407d
v4.4.0.0.alpha0+ Build ID: e21f6e3838a64f6c2517479d021e943e2ffcab94 TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time: 2014-10-10_09:04:45

Even selecting "Date (YMD)" on the column is unnecessary. The "Detect special numbers" option seems to be sufficient to import all values as dates, which can then be formatted to display as required.
Comment 8 ariel cornejo 2014-10-12 13:19:11 UTC
(In reply to Owen Genat from comment #7)
> (In reply to ariel cornejo from comment #5)
> > I just tried with version 4.4.0.0.alpha0
> > (24fb87501ef9d5aa715d572de7eb5efe49a0d9c3). Values in rows
> > 4,14,15,25,26,36-40 are imported as text:
> 
> That is quite a strange result. I have no problem here using:
> 
> v4.1.6.2 Build ID: 40ff705089295be5be0aae9b15123f687c05b0a
> v4.2.6.3 Build ID: 3fd416d4c6db7d3204c17ce57a1d70f6e531ee21
> v4.3.2.2 Build ID: edfb5295ba211bd31ad47d0bad0118690f76407d
> v4.4.0.0.alpha0+ Build ID: e21f6e3838a64f6c2517479d021e943e2ffcab94
> TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time:
> 2014-10-10_09:04:45
>
> Even selecting "Date (YMD)" on the column is unnecessary. The "Detect
> special numbers" option seems to be sufficient to import all values as
> dates, which can then be formatted to display as required.

I just tried "Detect special numbers" and it works just like you say. Did you tick it for the tests you describe above?

Anyway, it's not obvious that it should be used and the conversion otherwise definitely shouldn't depend on the *value* of the decimal part. Something is failing silently. I'm intrigued by m.a.riosv's suggestion in #1 but am clueless as to why the conversion works so differently depending on where it's called from.
Comment 9 raal 2015-03-14 18:45:11 UTC
Import from file/open -> I can reproduce [column type "Date (YMD)"] with LibreOffice 3.5.0 
Build ID: d6cde02, linux
and
Version: 4.4.3.0.0+
Build ID: 8106e522c4ea2ae4441ec571579a38eeb6d9af04
TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:libreoffice-4-4, Time: 2015-03-13_12:39:32

Values in rows 4,14,15,25,26,36-40 are imported as text.

When I try to paste in linux, I get only limited import dialog where I can not select column type. Detect special numbers doesn't work in this case and all dates are imported as text.

In windows 7, LO 4.4.1  import from file/open or paste is the same - dialog where I can select column type "Date (YMD)". Values in rows 4,14,15,25,26,36-40 are imported as text.

Setting to new.
Comment 10 tommy27 2016-04-16 07:25:12 UTC Comment hidden (obsolete)
Comment 11 QA Administrators 2017-05-22 13:26:41 UTC
** Please read this message in its entirety before responding **

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present on a currently supported version of LibreOffice 
(5.2.7 or 5.3.3  https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the version of LibreOffice and 
your operating system, and any changes you see in the bug behavior
 
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave 
a short comment that includes your version of LibreOffice and Operating System

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)


If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3)

http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to "inherited from OOo";
4b. If the bug was not present in 3.3 - add "regression" to keyword


Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug-20170522