英文:
convert Excel decimal date time into time.Time
问题
在使用Golang解析Excel文件时，一个日期时间列被读取为44531.553587963，实际上表示的是时间12/01/2021 13:17:10。
我该如何将这个十进制表示转换为Golang中的time.Time对象？
英文:
While parsing an excel file in Golang, a date time column is getting read as 44531.553587963 which actually represents the time 12/01/2021 13:17:10.
How can I convert this decimal representation to time.Time object in go?
答案1
得分: 4
var in float64 = 44531.553587963
excelEpoch := time.Date(1899, 12, 30, 0, 0, 0, 0, time.UTC)
tm := excelEpoch.Add(time.Duration(in * float64(24 * time.Hour)))
fmt.Println(tm) // 2021-12-01 13:17:10.000003072 +0000 UTC
Excel时间通常（除非是奇怪的旧版本格式）是从1899年12月30日开始计算的天数。它本来应该是这样的，使得1.0等于1900-01-01的午夜，但是有人使用了一个错误的闰年算法，认为1900年2月有29天，实际上只有28天，所以从那时起所有的日期都有一个偏移量。为了不破坏现有的电子表格，他们保留了这个错误，所以要得到正确的日期（1900年后），你必须将纪元向前偏移一天，这样1.0就等于1989-12-31，0.0就等于1989-12-30。
无论如何，一旦我们知道了纪元，只需要进行一些类型转换，将一天的纳秒转换为浮点数，进行乘法运算，然后再将其转换回持续时间，并将该持续时间添加到纪元中。
英文:
var in float64 = 44531.553587963
excelEpoch := time.Date(1899, 12, 30, 0, 0, 0, 0, time.UTC)
tm := excelEpoch.Add(time.Duration(in * float64(24 * time.Hour)))
fmt.Println(tm) // 2021-12-01 13:17:10.000003072 +0000 UTC
Excel time is (usually, barring weird legacy versions of the format) measured in days since 12/30/1899. It was meant to be so that 1.0 would equal midnight at 1900-01-01, but someone used a faulty leap year algorithm that thought that February 1900 had 29 days, when it only had 28, so all dates after that point were off-by-1. Rather than break existing spreadsheets, they left that bug in place, so to get correct dates (post-1900) you have to offset the epoch back by one day, so 1.0 equals 1989-12-31 and 0.0 equals 1989-12-30.
Anyway, once we know the epoch it's just a little type juggling to convert one day in nanoseconds to a float, multiply, and then convert back to a duration, and add that duration to the epoch.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论