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