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