Javascript中的OADate到毫秒时间戳

2022-01-13 00:00:00 datetime timestamp javascript

我尝试做完全相反的事情:

I try to do the exact opposite of :

什么是javascript中的 DateTime.ToOADate() 的等价物?

从 OADate(自 1899 年 12 月 30 日以来的天数作为双精度值)创建毫秒日期(自 1970 年 1 月 1 日以来的毫秒数)

Create a millisecond date (number of milliseconds since 1/1/1970) from a OADate (number of days since 30 dec 1899 as a double value)

我的猜测是我可以这样做:

my guess is I can do like this :

this.unixTimeStampFromOADate = function( OADateFloat)
{
        var oaDateFor1970 = ?? ; //what's the value for 1/1/1970 in OADate format ?
        var delta = Math.floor(OADateFloat - oaDateFor1970);

        return delta*(1000*60*60*24); 
}

所以如果我是对的，我需要 OADate 格式的 1/1/1970 的值.如果我错了，请您建议另一种转换方法吗?

so if I'm right, I need the value for 1/1/1970 in OADate format. if I'm wrong, please can you suggest another conversion method ?

推荐答案

Javascript 日期使用的时间值是自 1970-01-01T00:00:00Z 以来的毫秒数.日期 1899-12-30 的时间值为 -2209197600000.

Javascript dates use a time value that is milliseconds since 1970-01-01T00:00:00Z. The time value for the date 1899-12-30 is -2209197600000.

要获取从那时起的天数，获取今天开始时午夜的毫秒数，从 OA 纪元中减去它，然后除以一天中的毫秒，然后得到绝对值.请注意，时间值都是 UTC，因此考虑了夏令时、闰年等.

To get the days since then, get the milliseconds for midnight at the start of today, subtract it from the OA epoch, divide by the ms in one day and get the absolute value. Note that the time values are all UTC so daylight saving, leap years, etc. are accounted for.

var epoch = new Date(1899, 11, 30); // 1899-12-30T00:00:00
var now = new Date();               // 2013-03-22T<current time>
now.setHours(0,0,0,0)               // 2013-03-22T00:00:00

var oaDate  = Math.abs((epoch - now) / 8.64e7); // 41355 for 2013-03-22

您可以在 这里 的某些日期对其进行测试(请注意，这些日期位于令人困惑的美国 m/d/yy 格式).

You can test it against some dates here (note that those dates are in the confusing US m/d/yy format).

抱歉，理解错了.这里有一些功能可以双向使用.

Sorry, got the sense backwards. Here are some functions to go both ways.

还花了一些时间来弄清楚它所说的OLE 自动化日期被实现为一个浮点数，其整数部分是 1899 年 12 月 30 日午夜之前或之后的天数"实际上是指 1899 年或之后-12-30 00:00:00 并且小数部分表示当天的时间除以 24".换句话说，虽然 1899-12-29 00:00:00 是 -1，但 `899-12-29 06:00:00 的值是 -1.25，而不是 -0.75.

Also took some time to work out that where it says "OLE Automation date is implemented as a floating-point number whose integral component is the number of days before or after midnight, 30 December 1899" actually means on or after 1899-12-30 00:00:00 and that the "fractional component represents the time on that day divided by 24". In other words, while 1899-12-29 00:00:00 is -1, the value for `899-12-29 06:00:00 is -1.25, not -0.75.

无论如何，这些功能现在似乎可以工作，但请彻底测试:

Anyhow, these functions seem to work now, but please test thoroughly:

var toOADate = (function () {
  var epoch = new Date(1899,11,30);
  var msPerDay = 8.64e7;

  return function(d) {
    var v = -1 * (epoch - d)/msPerDay;

    // Deal with dates prior to 1899-12-30 00:00:00
    var dec = v - Math.floor(v);

    if (v < 0 && dec) {
      v = Math.floor(v) - dec;
    }

    return v;
  }
}());


var fromOADate = (function() {
  var epoch = new Date(1899,11,30);
  var msPerDay = 8.64e7;

  return function(n) {
    // Deal with -ve values
    var dec = n - Math.floor(n);

    if (n < 0 && dec) {
      n = Math.floor(n) - dec;
    }

    return new Date(n*msPerDay + +epoch);
  }
}());

var now = new Date();
var oaNow = toOADate(now);
var now2 = fromOADate(oaNow);

alert('Today: ' + now + '
OADate: ' + oaNow + '
oaNow to Date: ' + now2);

OADate 的规范令人困惑，特别是处理负数的方式.

The specification for OADate is confusing, particularly the way negative numbers are handled.

函数的更新版本，使用本地日期值.

Updated version of functions, use local date values.

/* Convert a Microsoft OADate to ECMAScript Date
** Treat all values as local.
** @param {string|number} oaDate - OADate value
** @returns {Date}
*/
function dateFromOADate (oaDate) {
  // Treat integer part is whole days
  var days = parseInt(oaDate);
  // Treat decimal part as part of 24hr day, always +ve
  var ms = Math.abs((oaDate - days) * 8.64e7);
  // Add days and add ms
  return new Date(1899, 11, 30 + days, 0, 0, 0, ms);
}


/* Convert an ECMAScript Date to a Microsoft OADate
** Treat all dates as local.
** @param {Date} date - Date to convert
** @returns {Date}
*/
function dateToOADate (date) {
  var temp = new Date(date);
  // Set temp to start of day and get whole days between dates,
  var days = Math.round((temp.setHours(0,0,0,0) - new Date(1899, 11, 30)) / 8.64e7);
  // Get decimal part of day, OADate always assumes 24 hours in day
  var partDay = (Math.abs((date - temp) % 8.64e7) / 8.64e7).toFixed(10);
  return days + partDay.substr(1);
}

var now = new Date();
var x = dateToOADate(now);
console.log('Now: ' + now.toString());
console.log('As an OADate: ' + x);
console.log('Back to date: ' + dateFromOADate(x).toString());

window.onload = function(){ 
  var el = document.getElementById('in')
  el.addEventListener('change', function() {
    var oaDate = dateToOADate(new Date(new Date(el.value)));
    document.getElementById('out').value = oaDate;
    document.getElementById('out2').value = dateFromOADate(oaDate);
  });
}

input {width: 25em}

<table>
  <tr>
    <td>Input date:<br>(DD MMM YYYY HH:mm)
    <td><input id="in" value="29 Dec 1899 06:00">
  <tr>
    <td>OA Date:
    <td><input id="out" readonly>
  <tr>
    <td>Back to standard date:
    <td><input id="out2" readonly>
</table>

相关文章