The TIMESTAMP_FROM_DATETIME function takes a timestamp – the number of milliseconds (or seconds) from 00:00:00 UTC, 1 January 1970 – and outputs the DateTime this number represents.
This function takes any number as input. By default, this number represents some number of milliseconds from 00:00:00 UTC, 1 January 1970 (the Unix epoch), and DATE_FROM_TIMESTAMP will output the DateTime corresponding with this number. Said number can also be given in seconds, if indicated by the optional input string.
Declaration
DATETIME_FROM_TIMESTAMP(number, unit_string) -> date_time
Parameters
number (required, type: number)
A number of milliseconds (default) or seconds from 00:00:00 UTC, 1 January 1970, the Unix epoch.
unit_string (optional, type: string)
A string representation of the units of the input ("s" for seconds or "ms" for milliseconds).
While DATETIME_FROM_TIMESTAMP 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
date_time (type: datetime)
The DateTime corresponding with the given number of milliseconds (or seconds) from 00:00:00 UTC, 1 January 1970, the Unix epoch.
Examples
The following example takes the number 1617240194000, interprets it as a 1617240194000 milliseconds after the Unix epoch, and outputs the corresponding DateTime. Note that the timezone of the resulting DateTime is UTC; this is timezone the DateTime resulting from DATETIME_FROM_TIMESTAMP will always be in:
DATETIME_FROM_TIMESTAMP(1617240194000) -> {
"date": {
"day": 1,
"month": 4,
"year": 2021
},
"time": {
"hour": 10,
"minute": 23,
"second": 14,
"millisecond": 0
},
"timeZone": "UTC"
}
The above example interprets the given number as milliseconds from from the Unix epoch because milliseconds are the default unit. If given DATETIME_FROM_TIMESTAMP a number describing time from the Unix epoch in seconds that 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 number 1617240194, interprets it as a 1617240194 seconds after the Unix epoch, and outputs the corresponding DateTime:
DATETIME_FROM_TIMESTAMP(1617240194, "s") -> {
"date": {
"day": 1,
"month": 4,
"year": 2021
},
"time": {
"hour": 10,
"minute": 23,
"second": 14,
"millisecond": 0
},
"timeZone": "UTC"
}
The optional variable unit_string can also be used to explicitly specify that the input number is in milliseconds; in such cases, the output will be the same as though the default was used. The following example takes the number 1617240194000, interprets it as a 1617240194000 milliseconds ("ms") after the Unix epoch, and outputs the corresponding DateTime. Note that the output as the same as it was in the first example, when no optional input unit_string was entered:
DATETIME_FROM_TIMESTAMP(1617240194000, "ms") -> {
"date": {
"day": 1,
"month": 4,
"year": 2021
},
"time": {
"hour": 10,
"minute": 23,
"second": 14,
"millisecond": 0
},
"timeZone": "UTC"
}
Technically, the DATETIME_FROM_TIMESTAMP 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, DATETIME_FROM_TIMESTAMP will proceed to interpret the given number in milliseconds, the default units. The following example takes the number 1617240194000, interprets it as a 1617240194000 milliseconds after the Unix epoch, and outputs the corresponding DateTime: 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:
DATETIME_FROM_TIMESTAMP(1617240194000, "ms") -> {
"date": {
"day": 1,
"month": 4,
"year": 2021
},
"time": {
"hour": 10,
"minute": 23,
"second": 14,
"millisecond": 0
},
"timeZone": "UTC"
}
Discussion
DATETIME_FROM_TIMESTAMP can be thought of as the opposite of TIMESTAMP_FROM_DATETIME; this is, assuming the giving DateTime is in the UTC timezone (call our example date_time), DATETIME_FROM_TIMESTAMP can be used to undo what TIMESTAMP_FROM_DATETIME did, like so:
DATETIME_FROM_TIMESTAMP(TIMESTAMP_FROM_DATETIME(date_time)) -> date_time
DATETIME_FROM_TIMESTAMP always returns DateTimes in UTC, and so the above example does not work out perfectly if date_time is not initially given in the UTC timezone. Crucially, however, the given DateTime and the return DateTime will refer to the same time in an absolute sense; it is only the formatting that may be slightly different due to timezone shifts.
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 it into other formats more legible to humans. This is the primary purpose of DATETIME_FROM_TIMESTAMP.