Excel – Converting From UNIX Time

Today I was working on a one-time HIVE report which due to the amount of data that is in each table, took a significant amount of time to run. After running though, I had the data that I wanted except for the fact that I neglected to convert the UNIX timestamp into a text version of the date. Instead of re-running the query again, I decided to use a formula in order to convert the UNIX time into a meaningful string.

  1. First I used =DATEVALUE("01/01/1970") in order to get the number representation of the first day in UNIX time (January 1, 1970).
  2. Next I determined that UNIX time must be divided by the amount of seconds in a day (86400) in order to be in accordance with the way Excel converts dates to numbers.
  3. Finally I ended up with the following formula in order to convert the UNIX time stored in cell A2 into a text representation: =TEXT(A2/86400+25569,"YYYY-MM-DD HH:MM:SS")

It is important that there is no built-in way to determine the timezone offset of the current users computer with a function. On the other hand, if you already know the timezone offset, you could include this in your calculations. For instance, if the data was captured in a timezone of -0400, you could use the following formula: =TEXT([UNIX_TIME_CELL]/86400+25569-4/24,"YYYY-MM-DD HH:MM:SS").

JavaScript – Date Difference Function

A few months ago I wrote a blog post about extensions to the Date.prototype. That post provided ways to add date parts and subtract date parts. Today’s post shows you how to use a generic Date.prototype.diff() function to find the difference between two dates and optionally return a string specifying the difference. This can be particularly useful if you need to show a countdown until a certain time.

The following is the code that I wrote for a comprehensive date difference function:

// Date.prototype.diff() - By Chris West - MIT Licensed
(function() {
  var SEC = 1e3,
      MIN = 6e4,
      HOUR = 36e5,
      DAY = 864e5,
      WEEK = 6048e5,
      reFormat = /'(''|[^']+)*'|\{([^}\\]+|\\[\s\S])+(?!\\)\}|-?([WY])\3*|-?([SsmhdDM])\4*%?|<[12]:(\w+)(?::(0+))?>/g,
      reSlashChars = /\\(.)/g,
      reStartComma = /^,/,
      reType = /\w%?$/,
      reSplit = /,(\\[\s\S]|[^\\,]+)*/g,
      reWords = /\W/g;
  Date.prototype.diff = function(endDate, format) {
    var me = new Date(Math.min(this, endDate));
    endDate = new Date(Math.max(this, endDate));
    var type,
        neg = +me != +this ? -1 : 1,
        diff = endDate - me,
        types = {
          S : diff,
          "S%" : diff % SEC,
          s : type = parseInt(diff / SEC),
          "s%" : type % 60,
          m : type = parseInt(diff / MIN),
          "m%" : type % 60,
          h : type = parseInt(diff / HOUR),
          "h%" : type % 24,
          d : type = parseInt(diff / DAY),
          "d%" : type % 7,
          D : type,
          "D%" : (type = endDate.getDate() - me.getDate()) < 0 ? endDate.getDate() + parseInt((new Date(me.getFullYear(), me.getMonth() + 1, 1) - me) / DAY) : type,
          W : parseInt(diff / WEEK),
          M : type = 12 * (endDate.getFullYear() - me.getFullYear()) + endDate.getMonth() - me.getMonth() - (endDate.getDate() < me.getDate()),
          "M%" : type % 12,
          Y : parseInt(type / 12)
        };
    if(neg < 0) {
      for(var i in types) {
        types[i] *= -1;
      }
    }
    types["-"] = neg > 0;
    return format
      ? format.replace(reFormat, function(match, index, $2, $3, $4, datePart, datePartLength) {
        if(match.charAt(0) == "'") {
          return match.slice(1, -1);
        }
        if(datePart) {
          index = match.charAt(1) == "1" ? me : endDate;
          try {
            return padNum(index["get" + datePart]() + (datePart == "Month" ? 1 : 0), (datePartLength || "").length);
          }
          catch(e) {
            return match;
          }
        }
        if(match.charAt(0) == "{") {
          index = [];
          ("," + match.slice(1, -1)).replace(reSplit, function(a) {
            index.push(a.slice(1));
          });
          console.log(index);
          if(index[2] in types) {
            type = types[index[2]];
          }
          return (index[+!(Math.abs(type) - 1)] || "").replace(reSlashChars, "$1");
        }
        type = types[match.match(reType)[0]];
        index = (type < 0 && match.charAt(0) != "-" ? -1 : 1) * type + "";
        match = match.replace(reWords, "");
        return padNum(index, match.length);
      })
      : types;
  };

  function padNum(num, digits) {
    var neg = (num = num + "").indexOf("-") == 0;
    return (neg ? "-" : "") + (new Array(Math.max(digits - (num).length + 1 + neg, 1))).join(0) + num.replace("-", "");
  }
})();

Let’s say that we have an SPAN element whose id attribute is countdown. If we wanted a countdown until the end of the year to show up in that element, we could write something like the following:

setInterval(function() {
  var now = new Date,
      nextYear = new Date(now.getFullYear() + 1 + "/01/01"),
      format = "M 'month'{s}, D% 'day'{s}, h% 'hour'{s}, m% 'minute'{s}, s% 'second'{s}";
      span = document.getElementById("countdown");
  span.innerHTML = now.diff(nextYear, format);
}, 1000);

The following JSBin example gives you the ability to experiment with the format string:
JS Bin

The following are more example date differences and format strings that could be used:

alert(new Date("2012/11/08 14:17:00").diff(new Date, "D:hh%:mm%:ss%:SSS%"));
alert(new Date("2012/10/09 14:17:00").diff(new Date, "D 'day'{s}, h% 'hour'{s}, m% 'minute'{s}, s%.SSS% 'seconds'"));
alert(new Date("2012/10/09 14:17:00").diff(new Date, "D 'día'{s}, h% 'hora'{s}, m% 'minuto'{s}, s% 'segundo'{s}"));

String Literals

In order to use string literals in a format string, you must surround your string in single quotes. In order to escape the single, simply prepend it with another single quote.

Date Difference Measurements

There are two different measurements for date differences: full measurements and remainder measurements. All remainder measurements are suffixed by percent signs following the meta characters. The following is a table of all of the date difference meta characters and the corresponding measurements:

Meta Character(s) Measurement
Y The amount of full years between the two dates.
W The amount of full weeks between the two dates.
M The amount of total months between the two dates.
M% The amount of months remaining, not including those counted in the total amount of years between the two dates.
D The amount of total days between the two dates.
D% The amount of days remaining, not including those counted in the total amount of months between the two dates.
d The amount of total days between the two dates. The same as D.
d% The amount of days remaining, not including those counted in the total amount of weeks between the two dates.
h The amount of total hours between the two dates.
h% The amount of hours remaining, not including those counted in the total amount of days between the two dates.
m The amount of total minutes between the two dates.
m% The amount of minutes remaining, not including those counted in the total amount of hours between the two dates.
s The amount of total seconds between the two dates.
s% The amount of seconds remaining, not including those counted in the total amount of minutes between the two dates.
S The amount of total milliseconds between the two dates.
s% The amount of milliseconds remaining, not including those counted in the total amount of seconds between the two dates.

Padding Measurements with Zeroes

You can pad a measurement with leading zeroes if too short. In order to make sure that a non-remainder measurement has at least two digits, you can simply append the same meta character to the end of itself. The amount of times that a meta character appears next to itself will determine the minimum number of digits to display. The following are examples:

  • hh – two digits for the total amount of hours
  • mm – two digits for the total amount of minutes
  • ss – two digits for the total amount of seconds
  • SSS – three digits for the total amount of milliseconds

In order to make sure that remainder measurements are padded, you can use the same principle. Only duplicate the first character of the meta character and prepend it as many times as you want. The amount of times that a meta character appears (not including the percent sign) determines the minimum number of digits to display. The following are examples:

  • hh% – two digits for the amount of hours (excluding those counted in the amount of days)
  • mm% – two digits for the amount of minutes (excluding those counted in the amount of hours)
  • ss% – two digits for the amount of seconds (excluding those counted in the amount of minutes)
  • SSS% – three digits for the amount of milliseconds (excluding those counted in the amount of seconds)

Negative Measurements

By default, negative numbers will not be shown. In order to show them, you will need to prefix the meta character string with a negative sign.

Conditional Literal Strings (1-based)

To specify which strings will appear if the meta character was 1 or not 1 you will have to use following format: {DISPLAY_IF_ONE,DISPLAY_IF_NOT_ONE,META_CHARACTER}.

Section Optional Description
DISPLAY_IF_ONE No The string to display if the meta character is 1
DISPLAY_IF_NOT_ONE Yes The string to display if the meta character is NOT 1.
META_CHARACTER Yes The singular meta character that will be used as the conditional to determine which of the two preceding strings to display. If not given, this will default to the most recently seen meta character in the entire format string.

Escaping Special Characters In Conditional Literal Strings

If you want a special character such as a comma or a curly bracket to display, you will need to prefix it with a backslash character.

JavaScript – Date.prototype.format()

Sometimes you write code that you wish you could totally rework. That is how I feel about jPaq’s Date.prototype.format(). The real issue I have with it is that the metacharacters are not intuitive. Therefore, I decided to write a new version of this function which is more intuitive. The following is the definition:

// Date.prototype.format() - By Chris West - MIT Licensed
(function() {
  var D = "Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday".split(","),
      M = "January,February,March,April,May,June,July,August,September,October,November,December".split(",");
  Date.prototype.format = function(format) {
    var me = this;
    return format.replace(/a|A|Z|S(SS)?|ss?|mm?|HH?|hh?|D{1,4}|M{1,4}|YY(YY)?|'([^']|'')*'/g, function(str) {
      var c1 = str.charAt(0),
          ret = str.charAt(0) == "'"
          ? (c1=0) || str.slice(1, -1).replace(/''/g, "'")
          : str == "a"
            ? (me.getHours() < 12 ? "am" : "pm")
            : str == "A"
              ? (me.getHours() < 12 ? "AM" : "PM")
              : str == "Z"
                ? (("+" + -me.getTimezoneOffset() / 60).replace(/^\D?(\D)/, "$1").replace(/^(.)(.)$/, "$10$2") + "00")
                : c1 == "S"
                  ? me.getMilliseconds()
                  : c1 == "s"
                    ? me.getSeconds()
                    : c1 == "H"
                      ? me.getHours()
                      : c1 == "h"
                        ? (me.getHours() % 12) || 12
                        : (c1 == "D" && str.length > 2)
                          ? D[me.getDay()].slice(0, str.length > 3 ? 9 : 3)
                          : c1 == "D"
                            ? me.getDate()
                            : (c1 == "M" && str.length > 2)
                              ? M[me.getMonth()].slice(0, str.length > 3 ? 9 : 3)
                              : c1 == "m"
                                ? me.getMinutes()
                                : c1 == "M"
                                  ? me.getMonth() + 1
                                  : ("" + me.getFullYear()).slice(-str.length);
      return c1 && str.length < 4 && ("" + ret).length < str.length
        ? ("00" + ret).slice(-str.length)
        : ret;
    });
  };
})();

Description

Creates a string from the date using the specified format string. The following table can be used to get the desired results:

Format Output Meaning
YYYY 2012 Four-digit representation of the year.
YY 12 Two-digit representation of the year.
MMMM September Full textual representation of the month.
MMM Sep Three letter representation of the month.
MM 09 Month with the leading zero (two digits long).
M 9 Month without the leading zero.
DDDD Wednesday Full textual representation of the day of the week.
DDD Wed Three letter representation of the day of the week.
DD 03 Day of the month with leading zero (two digits long).
D 3 Day of the month without leading zeros.
HH 19 24-hour format of hour with leading zero (two digits long).
hh 07 12-hour format of hour with leading zero (two digits long).
H 19 24-hour format of hour without leading zeros.
h 7 12-hour format of hour without leading zeros.
mm 01 Minutes with the leading zero (two digits long).
m 1 Minutes without the leading zero.
ss 08 Seconds with the leading zero (two digits long).
s 8 Seconds without the leading zero.
a pm Lowercase am or pm.
A PM Uppercase AM or PM.
SSS 095 Milliseconds with leading zeros (three digits long).
S 95 Milliseconds without leading zeros.
Z -0400 Difference to Greenwich time (GMT) in hours.
'NO ''FORMAT'' HERE' NO 'FORMAT' HERE The specified string within the single quotes printed literally. To escape a single quote, you must prepend it with another single quote.
Date Used:  Wednesday September 3, 2012 19:01:08.095 GMT-0400 (EDT)

Parameters

  1. format
    The string that will be used to format the date.  Any of the formats shown in the previous table are acceptable.

Returns

The date formatted as specified by the format parameter.

Examples

Now let’s look at an example of calling this function to get what you want.

var format = "'It is now' h:mm:ss.SSS A 'on' DDDD, MMMM D, YYYY.";
alert((new Date).format(format));

JS Bin

Personally, I like this a lot better than the current version of the Date.prototype.format(). Let me know if you think it can be improved in some way.