Best timestamp format for CSV / Excel?

Question

I am writing a CSV file. I need to write timestamps that are accurate, at least until the second, and preferably up to the millisecond. What is the best format for timestamps in a CSV file so that they can be accurately and unambiguously analyzed using Excel with minimal user intervention?

Answer 1

For second precision, yyyy-MM-dd HH: mm: ss should do the trick.

I believe that Excel is not very good with fractions of a second (it loses them when interacting with the IIRC COM object - I will try to find the link and place it here).

Answer 2

An early proposal to use "yyyy-MM-dd HH: mm: ss" is fine, although I think Excel has a much more accurate time resolution than that. I find this post pretty believable (follow the thread and you will see a lot of arithmetic and experimenting with Excel), and if that is correct, there will be milliseconds. You can simply attack the decimal places at the end, that is, "yyyy-mm-dd hh: mm: ss.000".

You should be aware that Excel may not necessarily format the data (without human intervention) so that you can see all this accuracy. On my computer at work, when I set up CSV with the data "yyyy-mm-dd hh: mm: ss.000" (manually using Notepad), I get "mm: ss.0" in the cell and "m / d / yyyy hh: mm: ss AM / PM "in the formula bar.

To obtain the maximum information [1] transmitted in the cells without human intervention, you can divide your timestamp into a part of the date and a time part with the time part of only the second. It seems to me that Excel wants to give you no more than three visible "levels" (where fractions of a second is their own level) in any given cell, and you want seven: years, months, days, hours, minutes, seconds, and fractions of a second.

Or, if you don’t need a timestamp for human reading, but want it to be as accurate as possible, you may prefer to just keep a large number (internally, Excel uses only the number of days, including fractional days, starting from the date of the “era” )

---

[1] That is, numerical information. If you want to see as much information as possible, but don’t care about performing calculations with it, you can compose some format that Excel will definitely analyze as a string and thus be left alone; e.g. "Yyyymmdd.hhmmss.000".

Answer 3

"yyyy-MM-dd hh: mm: ss.000" format does not work in all locales. For some (at least Danish) "yyyy-MM-dd hh: mm: ss, 000" will work better.

Answer 4

I suppose if you used the double data type, recalculation in Excel will work fine.

Answer 5

As for time zones. I have to store the UTC offset in seconds from UTC, so that formulas in Excel / OpenOffice can ultimately localize datetimes. I found this to be easier than storing any number with 0 in front of it. -0900 was not well versed in any spreadsheet system, and importing was almost impossible to train people to do.

Answer 6

Go to the language settings on the control panel, then click "Formatting Options", select the language and select the actual date format for the selected locale used by default by Windows. Yes, this timestamp format is region sensitive. Excel uses these formats when analyzing CSV.

In addition, if the locale uses non-ASCII characters, you will have to emit CSV in the corresponding code page of the Unicode precoding ANSI code, for example. CP1251. Excel will not accept UTF-8.

Answer 7

"yyyy-mm-dd hh: mm: ss.000" format does not work in all locales. For some (at least Danish) "yyyy-mm-dd hh: mm: ss, 000" will work better.

as user662894 replied.

I want to add: do not try to get microseconds, for example, using datetimeype SQL Server datetime2: Excel cannot process more than 3 fractional seconds (i.e. milliseconds).

So, "yyyy-mm-dd hh: mm: ss.000000" will not work, and when Excel feeds this type of string (from a CSV file), it will perform rounding, not truncation.

This can be good, unless microsecond accuracy is important, in which case you better not run automatic data type recognition, but just save the string as a string ...

Answer 8

I would suggest that the ISO format is a good idea. ( Wikipedia article , also with time information)

Answer 9

Try MM / dd / yyyy hh: mm: ss format.

Java code to create an XML file.

xmlResponse.append ("MyDate>"). Add (this.formatDate (resultSet.getTimestamp ("Date"), "MM / dd / yyyy hh: mm: ss a")). Append ("");

 public String formatDate(Date date, String format) { String dateString = ""; if(null != date) { SimpleDateFormat dateFormat = new SimpleDateFormat(format); dateString = dateFormat.format(date); } return dateString; }