Converting integers to timestamps

README.md

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
);

main.go

package main

import (
	"fmt"
	"math"
	"time"
)

func ut(n int64) (unit time.Duration, t time.Time) {
	switch {
	case n >= -62135596800 && n <= 253402300799:
		return time.Second, time.Unix(n, 0)
	case n >= -62135596800000 && n <= 253402300799000:
		return time.Millisecond, time.Unix(n/1e3, n%1e3*1e6)
	case n >= -62135596800000000 && n <= 253402300799000999:
		return time.Microsecond, time.Unix(n/1e6, n%1e6*1e3)
	default:
		return time.Nanosecond, time.Unix(0, n)
	}
}

func main() {
	fmt.Printf("% 27s = time % 15s | % 22s | % 25s | % 28s | % 30s\n", "input @ unit", "", "input * 1ns  ", "input * 1µs  ", "input * 1ms  ", "input * 1s  ")

	ns := []int64{
		math.MinInt64,
		-62135596800000000,
		-62135596800000,
		-62135596800,
		int64(math.MinInt32),
		0,
		int64(math.MaxInt32),
		253402300799,
		1000000000000,
		253402300799000,
		1000000000000000,
		253402300799000999,
		1000000000000000000,
		math.MaxInt64,
	}
	for _, n := range ns {
		u, t := ut(n)
		fmt.Printf("% 20d @ % 4s = %s", n, u, t.Format(time.RFC3339))

		fmt.Printf(" | % 22s", okTime(time.Unix(0, n)))
		fmt.Printf(" | % 25s", okTime(time.Unix(n/1e6, n%1e6*1e3)))
		fmt.Printf(" | % 28s", okTime(time.Unix(n/1e3, n%1e3*1e6)))
		fmt.Printf(" | % 30s", okTime(time.Unix(n, 0)))
		fmt.Println()
	}
}

func okTime(t time.Time) string {
	f := t.Format(time.RFC3339)
	if y := t.Year(); y >= 0 && y < 10_000 {
		f += " ✓"
	} else {
		f += "  "
	}
	return f
}