The TIMESTAMP_FROM_DATETIME function returns the value of a DateTime in terms of milliseconds (or seconds) from 00:00:00 UTC, 1 January 1970.
This function takes as input any DateTime as well as an optional string indicating the desired units of the output. By default, it outputs the number of milliseconds from 00:00:00 UTC, 1 January 1970 (the Unix epoch) to the given date, but it can also output this number in seconds.
Declaration
TIMESTAMP_FROM_DATETIME(date_time, unit_string) -> timestamp_value
Parameters
date_time (required, type: DateTime)
Any DateTime.
unit_string (optional, type: string)
A string representation of the desired units of the output ("s" for seconds or "ms" for milliseconds).
While TIMESTAMP_FROM_DATETIME will accept any string as valid input, the only strings it recognizes are "s" (which stands for "seconds") and "ms" (which stands for "milliseconds"). If given a string other than these two, it will run using the default units, milliseconds.
Return Values
timestamp_value (type: number)
The number of milliseconds (default) or seconds from 00:00:00 UTC, 1 January 1970 (the Unix epoch) to the given DateTime.
Examples
It's possible to hardcode a DateTime, but it quickly gets cumbersome. To avoid this, assume all formulas have access to the following DateTime:
example_date_and_time = {
"date": {
"day": 1,
"month": 4,
"year": 2021
},
"time": {
"hour": 10,
"minute": 23,
"second": 14,
"millisecond": 0
},
"timeZone": "UTC"
}
The following example takes the time described in example_date_and_time and outputs the number of milliseconds it is from the Unix epoch:
TIMESTAMP_FROM_DATETIME(example_date_and_time) -> 1617240194000
The above example gives the results in milliseconds because milliseconds are the default. If units are required in seconds rather than milliseconds, units need to be declared explicitly by entering the string "s" (for "seconds") as the optional input variable unit_string. The following example takes the time described in example_date_and_time and outputs the number of seconds it is from the Unix epoch:
TIMESTAMP_FROM_DATETIME(example_date_and_time, "s") -> 1617240194
The optional variable unit_string can also be used to explicitly request output in milliseconds; in such cases, the output will be the same as though the default was used. The following example takes the time described in example_date_and_time and outputs the number of milliseconds ("ms") it is from the Unix epoch. Note that the output as the same as it was in the first example, when no optional input unit_string was entered:
TIMESTAMP_FROM_DATETIME(example_date_and_time, "ms") -> 1617240194000
Technically, the TIMESTAMP_FROM_DATETIME function will accept any string as a value for unit_string, but only "s" and "ms" provide it with any signal. If any other string is given for unit_string, TIMESTAMP_FROM_DATETIME will proceed to output the result in milliseconds, the default units. The following example takes the time described in example_date_and_time and outputs the number of milliseconds it is from the Unix epoch: the same output that was returned in first the example, when the optional input unit_string was not entered. The fact that the string "this is a string" is given as the unit_string changes nothing:
TIMESTAMP_FROM_DATETIME(example_date_and_time, "this is an string") -> 1617240194000
The DateTime used in all of the above examples provides a time and date in UTC, but this does not need to be the case. For the last example, assume the formula has access to the following DateTime object, which describes the date and time in a timezone six hours ahead of UTC:
example_date_and_time2 = {
"date": {
"day": 1,
"month": 4,
"year": 2021
},
"time": {
"hour": 10,
"minute": 23,
"second": 14,
"millisecond": 0
},
"timeZone": "Antarctica/Vostok"
}
The last example takes the time described in example_date_and_time2 and outputs the number of milliseconds it is from the Unix epoch. Note that shift in timezone is taken into account:
TIMESTAMP_FROM_DATETIME(example_date_and_time2) -> 1617218594000
Discussion
TIMESTAMP_FROM_DATETIME can be thought of as the opposite of DATETIME_FROM_TIMESTAMP; this is, for any given number (call our example n) TIMESTAMP_FROM_DATETIME can be used to undo what DATETIME_FROM_TIMESTAMP did, like so:
TIMESTAMP_FROM_DATETIME(DATETIME_FROM_TIMESTAMP(n)) -> n
00:00:00 UTC, 1 January 1970 is significant largely in the context of Unix time: 00:00:00 UTC, 1 January 1970 is the Unix epoch, and Unix time is the number of seconds that have elapsed from the Unix epoch. Because Unix time is widely used in file formats and operating systems, it is necessary to have a means of converting other formats of time keeping into Unix time. This is the primary purpose of TIMESTAMP_FROM_DATETIME.