I've read http://erack.org/blog/archives/8-LibreOffice-date-acceptance-patterns.html and I don't find this situation helpful. ISO 8601 is a standard and is not locale-independent. I don't care that much about the output/display format, but formats such as "2014-11-08T19:24:24" should always be recognised. User story: as a user with a a list of ISO 8601 timestamp, I want them to be consistently recognised as timestamps, so that they are displayed as dates and can be worked on as dates (e.g. to calculate time intervals/differences). Bug 72040 is related (or can be considered a blocker/subtask) but seems focused on a single ISO 8601 format and on display. I'm using 4.2.8.2-1.fc20 with Italian locale. My specific input was https://frdata.wikimedia.org/campaign-vs-amount.csv
Hello, in your .csv file is format of date as in bug 72040. I think you have to specify which other format Calc should accept, because ISO 8601 allows reduced precision. https://en.wikipedia.org/wiki/ISO_8601 setting as enhancement
Your .csv file opens with ISO dates recognized as date whatever locale I'm using (my default is French) with the box "Detect special numbers" checked.
(In reply to Nemo_bis from comment #0) > I've read > http://erack.org/blog/archives/8-LibreOffice-date-acceptance-patterns.html > and I don't find this situation helpful. > ... > User story: as a user with a a list of ISO 8601 timestamp, I want them to be > consistently recognised as timestamps, so that they are displayed as dates > and can be worked on as dates (e.g. to calculate time intervals/differences). > ... > I'm using 4.2.8.2-1.fc20 with Italian locale. My specific input was > https://frdata.wikimedia.org/campaign-vs-amount.csv So we have a test file. What are the repro steps to take and the RESULTS vs what you EXPECTED to see? Status -> NEEDINFO Thanks. (set status back to UNCONFIRMED after you provide those repro steps)
As comment 2 states, the full ISO 8601 date/time format is recognized if the "Detect special numbers" checkbox in the import dialogue is checked on. What maybe could be done is to detect the date/time also if that is unchecked, but that would require to apply the same exact cell format as people may want to export the file again and preserve the ISO format, for which at the moment the unchecked "special numbers" can be used. Removing NEEDINFO, changing summary as this is not related to date acceptance patterns but only the CSV import.
Thanks for figuring it out and sorry for jumping to conclusions based on docs. I'm still not sure what steps I'm supposed to follow, this is what I tried now: 0) LibreOffice 4.2.8.2-2.fc20; it-IT interface, locale and default document language 1) "libreoffice campaign-vs-amount.csv" 2) In the import window select UTF-8, check "detect special numbers", confirm I. Observed: The table is there, as is in the CSV. 3) Apply a cell format of any "date" kind to the date columns II. Expected: dates are displayed in the chosen format. III. Observed: dates remain in their "native" CSV format. 4) Apply any date function to any of the date cells, e.g. DATA.DIFF(E4;I4) III. Expected: the date function gives an output. IV. Observed: Err:511 5) Repeat steps 3-4 after changing locale to fr-FR in Tools > Options > Language settings > Language > Locale (but not interface, nor default document language, nor this document's language). V. Observed: the date patterns in the same options panel vary. III and IV again. Am I still doing something wrong?
Strange, import works with other locales (you could change in step 2 above), but not with it-IT. Also manual input of 2014-11-23T12:34:56 is not accepted as date in it-IT, but is in other locales. That's a bug, I'll investigate.
Split-off bug 89500 for this.
(In reply to Nemo_bis from comment #5) > I'm still not sure what steps I'm supposed to follow, this is what I tried > now: > [...] I also cannot get the auto detection of "special numbers" to work. My times are all in UTC and formatted as 2019-12-04T07:29:00Z. I tried the locales German/Germany, English/UK, English/USA with the same results for each. Is maybe UTC not supported?
(In reply to Dennis Schridde from comment #8) > (In reply to Nemo_bis from comment #5) > > I'm still not sure what steps I'm supposed to follow, this is what I tried > > now: > > [...] > > I also cannot get the auto detection of "special numbers" to work. My times > are all in UTC and formatted as 2019-12-04T07:29:00Z. I tried the locales > German/Germany, English/UK, English/USA with the same results for each. Is > maybe UTC not supported? P.S. I also tried setting the locale to French/France, to no avail. Version: 7.0.4.2 Build ID: 00(Build:2) CPU threads: 8; OS: Linux 5.11; UI render: GL; VCL: gtk3 Locale: de-DE (en_US.UTF-8); UI: en-US Gentoo official package Calc: threaded
Timezones are not supported at all, which is due to how date+time is stored in major spreadsheet applications, as days (and fractions) since a given null date with no means to store a timezone. You can do a search and replace with regular expressions to remove the trailing Z upon which the resulting date+time value will be accepted as cell content. This is off-topic for this RFE though.
Fwiw, ISO date(+time) is recognized if in the import dialog a column's type is set to "Date (YMD)", also if "Detect special numbers" is off, just that the display format is not set to a matching format but the document locale's standard date(+time) formats are used. Keeping this RFE to apply proper formats for these cases, so saving again can save with the intended format.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/9bcdfcb74dae7ccd07be7159c0e1deba110d2a6f Related: tdf#88359 CSV: choose proper ISO date(+time) format if detected It will be available in 7.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/99adfcad3517de6530c9a2f025c528001f543ae2 Resolves: tdf#88359 CSV: import strict ISO date(+time) with standard settings It will be available in 7.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/bb5968d199e5f185b5dbbc9be6b6bca992b94932 tdf#88359: sc: Add UItest It will be available in 7.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.