I tested with this file (117 Bytes, after download, delete.odt and leave .csv), and get no problem.
Would you share to test the first two lines of your file? Please, add your LibreOffice version, operating system, and zoom level. Thanks.
LibreOffice 7.2.7.2 on Windows 6.1.
Because your time stamps are text actually. Always check “Detect special numbers”
Thanks @Villeroy.
Now I see that the columns width are the width of the real numbers (no date-formatted), not the date. I just cleared formatting (Ctrl+M) for A3:B3.
a.csv.ods (117 Bytes)
Imported with option “detect special numbers” and column widths adjusted:
Calc interpretes dates as dates in the context of the specified import locale when that idiotic option is set. It is idiotic because you always want it set and by default it is unset.
In this particular case we are dealing with ISO time stamps. ISO time stamps import correctly with any locale.
2019-08-15 14:53:18 ISO
15.8.2019 14:53:18 same value, but requires German locale
8/15/2019 14:53:18 requires English(USA)
15/8/2019 14:53:18 requires English(non US)
When you remove the formatting, you get the true decimal cell value.
German 43.692,6203472222 is the same value as
English 43,692.6203472222
2019-08-15 14:53:18
15/8/2019 14:53:18
$43,692.62
43.692,62 €
All these values are the same numeric value with different formattings and all formulas processing this same value will always return the same result. The weekday of $43,692.62 is 5 which stands for Thursday because day #43692 was Thursday, the 15 of August 2019. The important thing is that you do not import dates, times, currencies etc. as literal text because this is a spreadsheet program and spreadsheet programs deal with numbers in the first place. Date strings and currency strings are useless. Phone “numbers”, zip codes and part “numbers” are identifiers and should be imported as strings by marking their columns as strings in the import dialog, otherwise you may get wrong identifiers with leading zeroes cut off.
For you it may be idiotic, for others it is not. The background is that importing anything else than numbers as numeric content and then formatting it somehow according to whatever rules will transform the data, and resaving to CSV it may end up as different data. Hence Calc does that only if requested.
And no, we’re not dealing with ISO time stamps here, because ISO requires the literal T time designator between date and time, and the data here is just another date+time format that nearly resembles ISO but is not. And indeed, if the data was 2019-08-15T14:53:18 then it would be imported as date+time accordingly formatted, regardless of how the Import special numbers option was set.
Since tdf#88359 for 7.3.
A few days ago, I helped someone on IRC with a CSV import; and when they realized that the import from that textual format could convert some data to numbers, they asked why that dangerous option is not off by default, to guarantee safety by default, and to require explicit configuration for any conversion. Please avoid declaring any PoV as the only one possible and sane. There are others with equally extreme but opposite PoVs. Thank you.
I frequently use dates in csv files and although I haven’t used excel for a while I don’t recall having problems with dates. I suspect excel uses the locale setting because I was even blissfully ignorant of commas being used as decimal separators in some countries.
.
Using calc I’m certainly aware that importing dates is always a problem and it doesn’t seem to be easy to fix after importing it. Sometimes I end up with a mix of text dates and numbers formatted as dates.
.
You can’t even use calc to reformat data and save it “as shown”: https://bugs.documentfoundation.org/show_bug.cgi?id=142553
Fair enough, they are not dates or times. After data is entered it is validated and stored.
.
If it works reasonably in excel then why not use the same approach (whatever it is). Excel wasn’t perfect, I recall using DMS (trigonometry) as the best option for hours and minutes to avoid rounding errors.
Because “hidden from view” is not “as shown” by display formats, but that’s in the bug’s comments somewhere.
That’s exactly why LibreOffice shouldn’t be trying to format the values as anything but text 
or at least give me the option upon opening to have it not do that. I mean, I thought the reason Calc shows a pre-opening pop-up box to set the CSV formatting is so that we can set our standard for that file…
I don’t see an option to “just show values as text”:
Sure there’s an option to “Format quoted fields as text”, which is fine if the fields are quoted… but most fields in CSV that I’ve worked with aren’t quoted.
So… did I just miss the option “display all values as-is text, don’t format them as ### or [some ridiculous E+# number, and other dumb stuff only ODT files should try to do]”?
If it did that the questions would all be “why can’t I import my values” instead.
You missed that each field’s column type can be set, either select and set individually or just select all and set to Text.
The purist may see any character codes contained in the “csv” file - even LF (or any traditional control characters) as data.
She’s welcome. There are simple means (in StarBasic e.g.) to read a file bytewise, and to interpret it at will.
The csv concept isn’t defined restrictive, but gives a lot of room for implementations to exert it in a way offering users efficient means to do/order what is supposed to be often needed.
In case of lacking consistency concerning the pair of source/generator at the one end and the interpreter/consumer at the other end you will get errors misunderstandings and even great disaster.
Compared with what may happen if some of the mentioned pairs refuse their duty concerning reliability, a missing automatism for the adaption of column widths (what was the original issue) is extremely banal. A click (top left corner: Select All) and a doubleclick ( on any one of the column separators) solve the problem . So does the one-line-code in Basic:
ThisComponent.CurrentController.ActiveSheet.Columns.OptimalWidth = True
It has been introduced because too many users complained about 12/3 or 2:3 or 3-1 being interpreted as dates or times. Declaring these strings as text was too much for them. This was a problem for some users.
OpenOffice 3 introduced this new option and turned it off by default. SInce 12 years numeric strings imported from csv is the most frequently reported problem with Calc because this option is turned off and nobody understands what a special number is.
The solution to this problem would be: Import pairs of numbers separated by date delimiters as string. A date consist of 3 numbers. Handle keyboard input differently from text import.
The better -and the only good and safe- way to get rid of the mess would be to exclusively communicate (including via any variant of csv) dates and date-time values obeying the existing standards given by ISO 8601.
A conversion to a numeric representation as preferred for spreadsheets can then be done (accepting the arbitrary “null-date” of the version) by the receiving person if she is knowing what he does. This can be done during import or later using formulas or whatever.
Calc even allows to calculate with ISO-near textual representations without any explicit conversion.
See attached little rexample. disask77899calculationWithDateTime.ods (13.4 KB)
Implicit conversions should be used with great care. 
For example, guess without the help of Calc the result of the calculation of the formula:
=LEN(-"1:")
"1:" is an hour. Why it don’t work without the minus sign?
Only when converted to a number.
Because the Len parameter must be text.
The given example =LEN(-"1:") does no longer work under V 7.3.3.2. It still worked “as expected” (by sokol92) under V 6.4.5. Now you need (e.g.) =LEN(-"1:0") to get the string accepted as a time and autocoverted respectively.
However, I wasn’t talking of absurd examples but of purposeful ones, and if you make sure to pass (basically) ISO 8601, the conversion is reliable (Zone info currently excluded)…
If the purpose, however, is to prove the absurities of the spreadsheet-Do, you can easily get any formula result (except an error message) to be shown as “I am wrong!”.
Only opinions now:
Far beyond the examples, I generally am doubting automatic conversion if not strictly and restrictively specified for any kind of a propgramming system. The shortest way to give a specification of the kind is to exclude strings generally, and to allow number conversion only “upward” to the more general type, only if there is a single chain of types, and only if the conversion always is done in one step by native means. Good old Pascal offers the usage of the type name as the name of a function converting to that target type.
We also have partly a mix-up of automatic conversion and of overloading operators or functions/routines/methods concerning their parameter types. When I studied programming a bit more seriously nearly 60 yeras ago there was no overloading and rarely auto-conversion. I didn’t miss them. But there was a starting uglyness pretending to be due to efficiency, ruled by FORTRAN, and still present in …
I expected and see 19 (length of "-0,0416666666666667"). By the way, in Excel you get the same result.
Version: 7.3.4.2 (x64) / LibreOffice Community
Build ID: 728fec16bd5f605073805c3c9e7c4212a0120dc5
CPU threads: 6; OS: Windows 10.0 Build 19044; UI render: default; VCL: win
Locale: ru-RU (ru_RU); UI: en-US
Calc: threaded
Version: 7.3.3.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 4; OS: Linux 5.13; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 1:7.3.3~rc2-0ubuntu0.20.04.1~lo1
Calc: threaded
The main problem with implicit conversions in Calc (and Excel) formulas is that the conversion rules are locale dependent (as you yourself have pointed out many times)…
Suprise. I actually tested =LEN(-"1:") in V 7.3.3.2 (Win 64) and got #VALUE!.
ISO 8601 is not locale dependent, and therefore what I recommend to exclusively use everywhere.
From my point of view the first main problem is bad teaching. To give an example: There is an officious specification in Germany by DIN 5008 for business letters, containing as a norm the ISO 8601 date-format (DIN 5008 – Wikipedia). Trained typists simply don’t know it, and regard its usage a crackpot idea. If I need to verify my credit card data via phone, I’m forced to use an idiotic 6-digit-format for my birthday: DDMMJJ (JJ in place of YY). Being 100+ years: How to?
The second main problem concerning spreadsheets specifically is the “compatibility with Excel”. I simply can’t be compatible with the results. It would require to be “as crazy as MS likes me to be”.