date.go 4.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147
  1. package xlsx
  2. import (
  3. "math"
  4. "time"
  5. )
  6. const (
  7. MJD_0 float64 = 2400000.5
  8. MJD_JD2000 float64 = 51544.5
  9. secondsInADay = float64((24*time.Hour)/time.Second)
  10. nanosInADay = float64((24*time.Hour)/time.Nanosecond)
  11. )
  12. var (
  13. timeLocationUTC, _ = time.LoadLocation("UTC")
  14. unixEpoc = time.Date(1970, time.January, 1, 0, 0, 0, 0, time.UTC)
  15. // In 1900 mode, Excel takes dates in floating point numbers of days starting with Jan 1 1900.
  16. // The days are not zero indexed, so Jan 1 1900 would be 1.
  17. // Except that Excel pretends that Feb 29, 1900 occurred to be compatible with a bug in Lotus 123.
  18. // So, this constant uses Dec 30, 1899 instead of Jan 1, 1900, so the diff will be correct.
  19. // http://www.cpearson.com/excel/datetime.htm
  20. excel1900Epoc = time.Date(1899, time.December, 30, 0, 0, 0, 0, time.UTC)
  21. excel1904Epoc = time.Date(1904, time.January, 1, 0, 0, 0, 0, time.UTC)
  22. // Days between epocs, including both off by one errors for 1900.
  23. daysBetween1970And1900 = float64(unixEpoc.Sub(excel1900Epoc)/(24 * time.Hour))
  24. daysBetween1970And1904 = float64(unixEpoc.Sub(excel1904Epoc)/(24 * time.Hour))
  25. )
  26. func TimeToUTCTime(t time.Time) time.Time {
  27. return time.Date(t.Year(), t.Month(), t.Day(), t.Hour(), t.Minute(), t.Second(), t.Nanosecond(), timeLocationUTC)
  28. }
  29. func shiftJulianToNoon(julianDays, julianFraction float64) (float64, float64) {
  30. switch {
  31. case -0.5 < julianFraction && julianFraction < 0.5:
  32. julianFraction += 0.5
  33. case julianFraction >= 0.5:
  34. julianDays += 1
  35. julianFraction -= 0.5
  36. case julianFraction <= -0.5:
  37. julianDays -= 1
  38. julianFraction += 1.5
  39. }
  40. return julianDays, julianFraction
  41. }
  42. // Return the integer values for hour, minutes, seconds and
  43. // nanoseconds that comprised a given fraction of a day.
  44. // values would round to 1 us.
  45. func fractionOfADay(fraction float64) (hours, minutes, seconds, nanoseconds int) {
  46. const (
  47. c1us = 1e3
  48. c1s = 1e9
  49. c1day = 24 * 60 * 60 * c1s
  50. )
  51. frac := int64(c1day*fraction + c1us/2)
  52. nanoseconds = int((frac%c1s)/c1us) * c1us
  53. frac /= c1s
  54. seconds = int(frac % 60)
  55. frac /= 60
  56. minutes = int(frac % 60)
  57. hours = int(frac / 60)
  58. return
  59. }
  60. func julianDateToGregorianTime(part1, part2 float64) time.Time {
  61. part1I, part1F := math.Modf(part1)
  62. part2I, part2F := math.Modf(part2)
  63. julianDays := part1I + part2I
  64. julianFraction := part1F + part2F
  65. julianDays, julianFraction = shiftJulianToNoon(julianDays, julianFraction)
  66. day, month, year := doTheFliegelAndVanFlandernAlgorithm(int(julianDays))
  67. hours, minutes, seconds, nanoseconds := fractionOfADay(julianFraction)
  68. return time.Date(year, time.Month(month), day, hours, minutes, seconds, nanoseconds, time.UTC)
  69. }
  70. // By this point generations of programmers have repeated the
  71. // algorithm sent to the editor of "Communications of the ACM" in 1968
  72. // (published in CACM, volume 11, number 10, October 1968, p.657).
  73. // None of those programmers seems to have found it necessary to
  74. // explain the constants or variable names set out by Henry F. Fliegel
  75. // and Thomas C. Van Flandern. Maybe one day I'll buy that jounal and
  76. // expand an explanation here - that day is not today.
  77. func doTheFliegelAndVanFlandernAlgorithm(jd int) (day, month, year int) {
  78. l := jd + 68569
  79. n := (4 * l) / 146097
  80. l = l - (146097*n+3)/4
  81. i := (4000 * (l + 1)) / 1461001
  82. l = l - (1461*i)/4 + 31
  83. j := (80 * l) / 2447
  84. d := l - (2447*j)/80
  85. l = j / 11
  86. m := j + 2 - (12 * l)
  87. y := 100*(n-49) + i + l
  88. return d, m, y
  89. }
  90. // Convert an excelTime representation (stored as a floating point number) to a time.Time.
  91. func TimeFromExcelTime(excelTime float64, date1904 bool) time.Time {
  92. var date time.Time
  93. var wholeDaysPart = int(excelTime)
  94. // Excel uses Julian dates prior to March 1st 1900, and
  95. // Gregorian thereafter.
  96. if wholeDaysPart <= 61 {
  97. const OFFSET1900 = 15018.0
  98. const OFFSET1904 = 16480.0
  99. var date time.Time
  100. if date1904 {
  101. date = julianDateToGregorianTime(MJD_0, excelTime+OFFSET1904)
  102. } else {
  103. date = julianDateToGregorianTime(MJD_0, excelTime+OFFSET1900)
  104. }
  105. return date
  106. }
  107. var floatPart = excelTime - float64(wholeDaysPart)
  108. if date1904 {
  109. date = excel1904Epoc
  110. } else {
  111. date = excel1900Epoc
  112. }
  113. durationPart := time.Duration(nanosInADay * floatPart)
  114. return date.AddDate(0,0, wholeDaysPart).Add(durationPart)
  115. }
  116. // TimeToExcelTime will convert a time.Time into Excel's float representation, in either 1900 or 1904
  117. // mode. If you don't know which to use, set date1904 to false.
  118. // TODO should this should handle Julian dates?
  119. func TimeToExcelTime(t time.Time, date1904 bool) float64 {
  120. // Get the number of days since the unix epoc
  121. daysSinceUnixEpoc := float64(t.Unix())/secondsInADay
  122. // Get the number of nanoseconds in days since Unix() is in seconds.
  123. nanosPart := float64(t.Nanosecond())/nanosInADay
  124. // Add both together plus the number of days difference between unix and Excel epocs.
  125. var offsetDays float64
  126. if date1904 {
  127. offsetDays = daysBetween1970And1904
  128. } else {
  129. offsetDays = daysBetween1970And1900
  130. }
  131. daysSinceExcelEpoc := daysSinceUnixEpoc + offsetDays + nanosPart
  132. return daysSinceExcelEpoc
  133. }