Table of integers and how they convert into timestamps at different resolutions.
A ✓
indicates the timestamp is within the range 0000-00-00T00:00:00Z to 9999-12-31T23:59:59.9999 which is considered valid by Google BigQuery.
The number 253402300799
is the unix timestamp (number of seconds since 1970-01-01T00:00:00Z) until 9999-12-31T23:59:59.9999 and is the recommended switch to the next resolution.
input @ unit = time | input * 1ns | input * 1µs | input * 1ms | input * 1s
-9223372036854775808 @ 1ns = 1677-09-21T00:12:43Z | 1677-09-21T00:12:43Z ✓ | -290308-12-21T19:59:05Z | -292275055-05-16T16:47:04Z | 292277026596-12-04T15:30:08Z
-62135596800000000 @ 1µs = 0001-01-01T00:00:00Z | 1968-01-12T20:06:43Z ✓ | 0001-01-01T00:00:00Z ✓ | -1967029-04-28T00:00:00Z | -1968996709-01-15T00:00:00Z
-62135596800000 @ 1ms = 0001-01-01T00:00:00Z | 1969-12-31T06:44:24Z ✓ | 1968-01-12T20:06:43Z ✓ | 0001-01-01T00:00:00Z ✓ | -1967029-04-28T00:00:00Z
-62135596800 @ 1s = 0001-01-01T00:00:00Z | 1969-12-31T23:58:57Z ✓ | 1969-12-31T06:44:24Z ✓ | 1968-01-12T20:06:43Z ✓ | 0001-01-01T00:00:00Z ✓
-2147483648 @ 1s = 1901-12-13T20:45:52Z | 1969-12-31T23:59:57Z ✓ | 1969-12-31T23:24:12Z ✓ | 1969-12-07T03:28:36Z ✓ | 1901-12-13T20:45:52Z ✓
0 @ 1s = 1970-01-01T00:00:00Z | 1970-01-01T00:00:00Z ✓ | 1970-01-01T00:00:00Z ✓ | 1970-01-01T00:00:00Z ✓ | 1970-01-01T00:00:00Z ✓
2147483647 @ 1s = 2038-01-19T03:14:07Z | 1970-01-01T00:00:02Z ✓ | 1970-01-01T00:35:47Z ✓ | 1970-01-25T20:31:23Z ✓ | 2038-01-19T03:14:07Z ✓
253402300799 @ 1s = 9999-12-31T23:59:59Z | 1970-01-01T00:04:13Z ✓ | 1970-01-03T22:23:22Z ✓ | 1978-01-11T21:31:40Z ✓ | 9999-12-31T23:59:59Z ✓
1000000000000 @ 1ms = 2001-09-09T01:46:40Z | 1970-01-01T00:16:40Z ✓ | 1970-01-12T13:46:40Z ✓ | 2001-09-09T01:46:40Z ✓ | 33658-09-27T01:46:40Z
253402300799000 @ 1ms = 9999-12-31T23:59:59Z | 1970-01-03T22:23:22Z ✓ | 1978-01-11T21:31:40Z ✓ | 9999-12-31T23:59:59Z ✓ | 8031969-03-31T23:43:20Z
1000000000000000 @ 1µs = 2001-09-09T01:46:40Z | 1970-01-12T13:46:40Z ✓ | 2001-09-09T01:46:40Z ✓ | 33658-09-27T01:46:40Z | 31690708-07-05T01:46:40Z
253402300799000999 @ 1µs = 9999-12-31T23:59:59Z | 1978-01-11T21:31:40Z ✓ | 9999-12-31T23:59:59Z ✓ | 8031969-03-31T23:43:20Z | 8030001217-01-16T10:29:59Z
1000000000000000000 @ 1ns = 2001-09-09T01:46:40Z | 2001-09-09T01:46:40Z ✓ | 33658-09-27T01:46:40Z | 31690708-07-05T01:46:40Z | 31688740476-10-23T01:46:40Z
9223372036854775807 @ 1ns = 2262-04-11T23:47:16Z | 2262-04-11T23:47:16Z ✓ | 294247-01-10T04:00:54Z | 292278994-08-17T07:12:55Z | 292277026596-12-04T15:30:07Z
https://go.dev/play/p/18TiLvugUbA
CREATE TEMPORARY FUNCTION TIMESTAMP_INT(n INT64) RETURNS TIMESTAMP AS (
CASE
WHEN n BETWEEN -62135596800 AND 253402300799 THEN TIMESTAMP_SECONDS(n)
WHEN n BETWEEN -62135596800000 AND 253402300799000 THEN TIMESTAMP_MILLIS(n)
WHEN n BETWEEN -62135596800000000 AND 253402300799000999 THEN TIMESTAMP_MICROS(n)
ELSE TIMESTAMP_MICROS(CAST(n/1000 AS INT64))
END
);