types

package
v0.3.1 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Apr 25, 2025 License: PostgreSQL Imports: 5 Imported by: 0

Documentation

Overview

Package types provides PostgresSQL-compatible data types for SQL/JSON Path execution.

It makes every effort to duplicate the behavior of PostgreSQL JSONB dates and times in particular in order to compatibly execute date and time comparisons in SQL/JSON Path expressions.

DateTime Types

Package maps the Postgres date and time types to these DateTime-implementing types:

Each provides a constructor that takes a time.Time object, which defines the underlying representation. Each also provides casting functions between the types, but only for supported casts.

Time Zones

Like the PostgreSQL timetz and timestamptz types, TimeTZ and TimestampTZ do not store time zone information, but an offset from UTC. Even when passed a time.Time object with a detailed location, the constructors will strip it out and retain only the offset for the time.Time value.

By default, the types package operates on and displays dates and times in the context of UTC. This affects conversion between time zone and non-time zone data types, in particular. To change the time zone in which such operations execute,

When required to operate on dates and times in the context of a time zone, the types package defaults to UTC. For example, a TimestampTZ stringifies into UTC: 

offsetPlus5 := time.FixedZone("", 5*3600)
timestamp := types.NewTimestampTZ(
	context.Background(),
	time.Date(2023, 8, 15, 12, 34, 56, 0, offsetPlus5),
)
fmt.Printf("%v\n", timestamp) // → 2023-08-15T07:34:56+00:00

To operate in a the context of a different time zone, use ContextWithTZ to add it to the context passed to any constructor or method that takes a context: 

tz, err := time.LoadLocation("America/New_York")
if err != nil {
	log.Fatal(err)
}
ctx := types.ContextWithTZ(context.Background(), tz)

offsetPlus5 := time.FixedZone("", 5*3600)
timestamp := types.NewTimestampTZ(
	ctx,
	time.Date(2023, 8, 15, 12, 34, 56, 0, offsetPlus5),
)

fmt.Printf("%v\n", timestamp)        // → 2023-08-15T07:34:56+00:00

This time zone affects casts, as well, between offset-aware types (TimeTZ, TimestampTZ) and offset-unaware types (Date, Time, Timestamp). For any execution, be sure to pass the same context to all operations.

Example (NYC)

Postgres: 

david=# set time zone 'America/New_York';
SET
david=# select jsonb_path_query_tz('"2023-08-15 12:34:56+05"', '$.timestamp_tz()');
     jsonb_path_query_tz
-----------------------------
 "2023-08-15T12:34:56+05:00"
(1 row)

david=# select jsonb_path_query_tz('"2023-08-15 12:34:56+05"', '$.timestamp_tz().string()');
   jsonb_path_query_tz
--------------------------
 "2023-08-15 03:34:56-04"
(1 row)

types.TimestampTZ: 

package main

import (
	"context"
	"fmt"
	"log"
	"time"

	"github.com/theory/sqljson/path/types"
)

func main() {
	tz, err := time.LoadLocation("America/New_York")
	if err != nil {
		log.Fatal(err)
	}
	ctx := types.ContextWithTZ(context.Background(), tz)

	offsetPlus5 := time.FixedZone("", 5*3600)
	timestamp := types.NewTimestampTZ(
		ctx,
		time.Date(2023, 8, 15, 12, 34, 56, 0, offsetPlus5),
	)

	fmt.Printf("%v\n", timestamp)
}

Output:

2023-08-15T12:34:56+05:00
Example (UTC)

Postgres: 

david=# select jsonb_path_query_tz('"2023-08-15 12:34:56+05"', '$.timestamp_tz()');
     jsonb_path_query_tz
-----------------------------
 "2023-08-15T12:34:56+05:00"
(1 row)

david=# select jsonb_path_query_tz('"2023-08-15 12:34:56+05"', '$.timestamp_tz().string()');
   jsonb_path_query_tz
--------------------------
 "2023-08-15 07:34:56+00"
(1 row)

types.TimestampTZ: 

package main

import (
	"context"
	"fmt"
	"time"

	"github.com/theory/sqljson/path/types"
)

func main() {
	offsetPlus5 := time.FixedZone("", 5*3600)
	ctx := types.ContextWithTZ(context.Background(), time.UTC)

	timestamp := types.NewTimestampTZ(
		ctx,
		time.Date(2023, 8, 15, 12, 34, 56, 0, offsetPlus5),
	)

	fmt.Printf("%v\n", timestamp)
}

Output:

2023-08-15T12:34:56+05:00

Index

Examples

Constants

This section is empty.

Variables

View Source 
var ErrSQLType = errors.New("type")

ErrSQLType wraps errors returned by the types package.

Functions

func ContextWithTZ 

func ContextWithTZ(ctx context.Context, tz *time.Location) context.Context

ContextWithTZ returns a new Context that carries value tz.

func TZFromContext 

func TZFromContext(ctx context.Context) *time.Location

TZFromContext returns the time.Location value stored in ctx or time.UTC.

Types

type Date 

type Date struct {
	time.Time
}

Date represents the PostgreSQL date type.

Example

Postgres: 

david=# set time zone 'America/New_York';
SET
david=# select jsonb_path_query_tz('"2023-08-15"', '$.date()');
 jsonb_path_query_tz
---------------------
 "2023-08-15"
(1 row)

david=# select jsonb_path_query_tz('"2023-08-15"', '$.timestamp()');
  jsonb_path_query_tz
-----------------------
 "2023-08-15T00:00:00"
(1 row)

david=# select jsonb_path_query_tz('"2023-08-15"', '$.timestamp_tz()');
     jsonb_path_query_tz
-----------------------------
 "2023-08-15T04:00:00+00:00"
(1 row)

types.Date: 

package main

import (
	"context"
	"fmt"
	"log"
	"time"

	"github.com/theory/sqljson/path/types"
)

func main() {
	date := types.NewDate(time.Date(2023, 8, 15, 12, 34, 56, 0, time.UTC))
	fmt.Printf("%v\n", date)

	tz, err := time.LoadLocation("America/New_York")
	if err != nil {
		log.Fatal(err)
	}
	ctx := types.ContextWithTZ(context.Background(), tz)

	fmt.Printf("%v\n", date.ToTimestamp(ctx))
	// Difference in cast value formatting thread:
	// https://www.postgresql.org/message-id/flat/7DE080CE-6D8C-4794-9BD1-7D9699172FAB%40justatheory.com
	fmt.Printf("%v\n", date.ToTimestampTZ(ctx))
}

Output:

2023-08-15
2023-08-15T00:00:00
2023-08-15T00:00:00-04:00

func NewDate 

func NewDate(src time.Time) *Date

NewDate coerces src into a Date.

func (*Date) Compare 

func (d *Date) Compare(u time.Time) int

Compare compares the time instant d with u. If d is before u, it returns -1; if d is after u, it returns +1; if they're the same, it returns 0.

func (*Date) GoTime 

func (d *Date) GoTime() time.Time

GoTime returns the underlying time.Time object.

func (*Date) MarshalJSON 

func (d *Date) MarshalJSON() ([]byte, error)

MarshalJSON implements the json.Marshaler interface. The time is a quoted string in the RFC 3339 format with sub-second precision.

func (*Date) String 

func (d *Date) String() string

String returns the string representation of d.

func (*Date) ToTimestamp 

func (d *Date) ToTimestamp(context.Context) *Timestamp

ToTimestamp converts ts to *Timestamp.

func (*Date) ToTimestampTZ 

func (d *Date) ToTimestampTZ(ctx context.Context) *TimestampTZ

ToTimestampTZ converts d to TimestampTZ in the time zone in ctx.

func (*Date) UnmarshalJSON 

func (d *Date) UnmarshalJSON(data []byte) error

UnmarshalJSON implements the json.Unmarshaler interface. The time must be a quoted string in the RFC 3339 format.

type DateTime 

type DateTime interface {
	fmt.Stringer
	// GoTime returns the underlying time.Time object.
	GoTime() time.Time
}

DateTime defines the interface for all date and time data types.

func ParseTime 

func ParseTime(ctx context.Context, src string, precision int) (DateTime, bool)

ParseTime parses src into time.Time by iterating through a list of valid date, time, and timestamp formats according to SQL/JSON standard: date, time_tz, time, timestamp_tz, and timestamp. Returns false if the string cannot be parsed by any of the formats.

We also support ISO 8601 format (with "T") for timestamps, because PostgreSQL to_json() and to_jsonb() functions use this format.

type Time 

type Time struct {
	// Time is the underlying time.Time value.
	time.Time
}

Time represents the PostgreSQL time without time zone type.

Example

Postgres: 

david=# set time zone 'America/Phoenix';
SET
david=# select jsonb_path_query_tz('"12:34:56"', '$.time()');
 jsonb_path_query_tz
---------------------
 "12:34:56"
(1 row)

david=# select jsonb_path_query_tz('"12:34:56"', '$.time_tz()');
 jsonb_path_query_tz
---------------------
 "12:34:56-07:00"
(1 row)

types.Time: 

package main

import (
	"context"
	"fmt"
	"log"
	"time"

	"github.com/theory/sqljson/path/types"
)

func main() {
	aTime := types.NewTime(time.Date(2023, 8, 15, 12, 34, 56, 0, time.UTC))
	fmt.Printf("%v\n", aTime)

	tz, err := time.LoadLocation("America/Phoenix")
	if err != nil {
		log.Fatal(err)
	}
	ctx := types.ContextWithTZ(context.Background(), tz)
	fmt.Printf("%v\n", aTime.ToTimeTZ(ctx))
}

Output:

12:34:56
12:34:56-07:00

func NewTime 

func NewTime(src time.Time) *Time

NewTime coerces src into a Time.

func (*Time) Compare 

func (t *Time) Compare(u time.Time) int

Compare compares the time instant t with u. If d is before u, it returns -1; if t is after u, it returns +1; if they're the same, it returns 0.

func (*Time) GoTime 

func (t *Time) GoTime() time.Time

GoTime returns the underlying time.Time object.

func (*Time) MarshalJSON 

func (t *Time) MarshalJSON() ([]byte, error)

MarshalJSON implements the json.Marshaler interface. The time is a quoted string using the "15:04:05.999999999" format.

func (*Time) String 

func (t *Time) String() string

String returns the string representation of ts using the format "15:04:05.999999999".

func (*Time) ToTimeTZ 

func (t *Time) ToTimeTZ(ctx context.Context) *TimeTZ

ToTimeTZ converts t to *TimeTZ in the time zone in ctx. It works relative the current date.

func (*Time) UnmarshalJSON 

func (t *Time) UnmarshalJSON(data []byte) error

UnmarshalJSON implements the json.Unmarshaler interface. The time must be a quoted string in the "15:04:05.999999999" format.

type TimeTZ 

type TimeTZ struct {
	// Time is the underlying time.Time value.
	time.Time
}

TimeTZ represents the PostgreSQL time with time zone type.

Example

Postgres: 

david=# set time zone 'UTC';
SET
david=# select jsonb_path_query_tz('"12:34:56-04:00"', '$.time_tz()');
 jsonb_path_query_tz
---------------------
 "12:34:56-04:00"
(1 row)

david=# select jsonb_path_query_tz('"12:34:56-04:00"', '$.time()');
 jsonb_path_query_tz
---------------------
 "12:34:56"
(1 row)

david=# set time zone 'America/New_York';
SET
david=# select jsonb_path_query_tz('"12:34:56-04:00"', '$.time()');
 jsonb_path_query_tz
---------------------
 "12:34:56"
(1 row)

types.TimeTZ: 

package main

import (
	"context"
	"fmt"
	"log"
	"time"

	"github.com/theory/sqljson/path/types"
)

func main() {
	tz, err := time.LoadLocation("America/New_York")
	if err != nil {
		log.Fatal(err)
	}

	timeTZ := types.NewTimeTZ(time.Date(2023, 8, 15, 12, 34, 56, 0, tz))
	fmt.Printf("%v\n", timeTZ)

	ctx := types.ContextWithTZ(context.Background(), time.UTC)
	fmt.Printf("%v\n", timeTZ.ToTime(ctx))

	//nolint:gosmopolitan
	ctx = types.ContextWithTZ(context.Background(), time.Local)
	fmt.Printf("%v\n", timeTZ.ToTime(ctx))
}

Output:

12:34:56-04:00
12:34:56
12:34:56

func NewTimeTZ 

func NewTimeTZ(src time.Time) *TimeTZ

NewTimeTZ coerces src into a TimeTZ.

func (*TimeTZ) Compare 

func (t *TimeTZ) Compare(u time.Time) int

Compare compares the time instant t with u. If d is before u, it returns -1; if t is after u, it returns +1; if they're the same, it returns 0. Note that the TZ offset contributes to this comparison; values with different offsets are never considered to be the same.

func (*TimeTZ) GoTime 

func (t *TimeTZ) GoTime() time.Time

GoTime returns the underlying time.Time object.

func (*TimeTZ) MarshalJSON 

func (t *TimeTZ) MarshalJSON() ([]byte, error)

MarshalJSON implements the json.Marshaler interface. The time is a quoted string using the "15:04:05.999999999-07:00" format.

func (*TimeTZ) String 

func (t *TimeTZ) String() string

String returns the string representation of ts using the format "15:04:05.999999999-07:00".

func (*TimeTZ) ToTime 

func (t *TimeTZ) ToTime(context.Context) *Time

ToTime converts t to *Time.

func (*TimeTZ) UnmarshalJSON 

func (t *TimeTZ) UnmarshalJSON(data []byte) error

UnmarshalJSON implements the json.Unmarshaler interface. The time must be a quoted string in one of the following formats:

  • 15:04:05.999999999Z07:00:00
  • 15:04:05.999999999Z07:00
  • 15:04:05.999999999Z07

type Timestamp 

type Timestamp struct {
	// Time is the underlying time.Time value.
	time.Time
}

Timestamp represents the PostgreSQL timestamp without time zone type.

Example

Postgres: 

david=# set time zone 'America/Phoenix';
SET
david=# select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp()');
  jsonb_path_query_tz
-----------------------
 "2023-08-15T12:34:56"
(1 row)

david=# select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.date()');
 jsonb_path_query_tz
---------------------
 "2023-08-15"
(1 row)

david=# select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.time()');
 jsonb_path_query_tz
---------------------
 "12:34:56"
(1 row)

david=# select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz()');
     jsonb_path_query_tz
-----------------------------
 "2023-08-15T19:34:56+00:00"
(1 row)

types.Timestamp: 

package main

import (
	"context"
	"fmt"
	"log"
	"time"

	"github.com/theory/sqljson/path/types"
)

func main() {
	ts := types.NewTimestamp(time.Date(2023, 8, 15, 12, 34, 56, 0, time.UTC))
	fmt.Printf("%v\n", ts)

	tz, err := time.LoadLocation("America/Phoenix")
	if err != nil {
		log.Fatal(err)
	}
	ctx := types.ContextWithTZ(context.Background(), tz)
	fmt.Printf("%v\n", ts.ToDate(ctx))
	fmt.Printf("%v\n", ts.ToTime(ctx))
	// Difference in cast value formatting thread:
	// https://www.postgresql.org/message-id/flat/7DE080CE-6D8C-4794-9BD1-7D9699172FAB%40justatheory.com
	fmt.Printf("%v\n", ts.ToTimestampTZ(ctx))
}

Output:

2023-08-15T12:34:56
2023-08-15
12:34:56
2023-08-15T12:34:56-07:00

func NewTimestamp 

func NewTimestamp(src time.Time) *Timestamp

NewTimestamp coerces src into a Timestamp.

func (*Timestamp) Compare 

func (ts *Timestamp) Compare(u time.Time) int

Compare compares the time instant ts with u. If ts is before u, it returns -1; if ts is after u, it returns +1; if they're the same, it returns 0.

func (*Timestamp) GoTime 

func (ts *Timestamp) GoTime() time.Time

GoTime returns the underlying time.Time object.

func (*Timestamp) MarshalJSON 

func (ts *Timestamp) MarshalJSON() ([]byte, error)

MarshalJSON implements the json.Marshaler interface. The time is a quoted string using the "2006-01-02T15:04:05.999999999" format.

func (*Timestamp) String 

func (ts *Timestamp) String() string

String returns the string representation of ts using the format "2006-01-02T15:04:05.999999999".

func (*Timestamp) ToDate 

func (ts *Timestamp) ToDate(context.Context) *Date

ToDate converts ts to *Date.

func (*Timestamp) ToTime 

func (ts *Timestamp) ToTime(context.Context) *Time

ToTime converts ts to *Time.

func (*Timestamp) ToTimestampTZ 

func (ts *Timestamp) ToTimestampTZ(ctx context.Context) *TimestampTZ

ToTimestampTZ converts ts to *TimestampTZ.

func (*Timestamp) UnmarshalJSON 

func (ts *Timestamp) UnmarshalJSON(data []byte) error

UnmarshalJSON implements the json.Unmarshaler interface. The time must be a quoted string in the "2006-01-02T15:04:05.999999999" format.

type TimestampTZ 

type TimestampTZ struct {
	// Time is the underlying time.Time value.
	time.Time
	// contains filtered or unexported fields
}

TimestampTZ represents the PostgreSQL timestamp with time zone type.

Example

Postgres: 

david=# set time zone 'UTC';
SET
david=# select jsonb_path_query_tz('"2023-08-15 12:34:56-04"', '$.timestamp_tz()');
     jsonb_path_query_tz
-----------------------------
 "2023-08-15T12:34:56-04:00"
(1 row)

david=# select jsonb_path_query_tz('"2023-08-15 12:34:56-04"', '$.timestamp()');
  jsonb_path_query_tz
-----------------------
 "2023-08-15T16:34:56"
(1 row)

david=# select jsonb_path_query_tz('"2023-08-15 12:34:56-04"', '$.date()');
 jsonb_path_query_tz
---------------------
 "2023-08-15"
(1 row)

david=# select jsonb_path_query_tz('"2023-08-15 12:34:56-04"', '$.time()');
 jsonb_path_query_tz
---------------------
 "16:34:56"
(1 row)

david=# set time zone 'America/Los_Angeles';
david=# select jsonb_path_query_tz('"2023-08-15 12:34:56-04"', '$.timestamp()');
  jsonb_path_query_tz
-----------------------
 "2023-08-15T09:34:56"
(1 row)

david=# select jsonb_path_query_tz('"2023-08-15 12:34:56-04"', '$.date()');
 jsonb_path_query_tz
---------------------
 "2023-08-15"
(1 row)

david=# select jsonb_path_query_tz('"2023-08-15 12:34:56-04"', '$.time()');
 jsonb_path_query_tz
---------------------
 "09:34:56"
(1 row)

types.TimestampTZ: 

package main

import (
	"context"
	"fmt"
	"log"
	"time"

	"github.com/theory/sqljson/path/types"
)

func main() {
	tz, err := time.LoadLocation("America/New_York")
	if err != nil {
		log.Fatal(err)
	}

	ctx := types.ContextWithTZ(context.Background(), time.UTC)
	tsTZ := types.NewTimestampTZ(ctx, time.Date(2023, 8, 15, 12, 34, 56, 0, tz))
	fmt.Printf("%v\n", tsTZ)
	fmt.Printf("%v\n", tsTZ.ToTimestamp(ctx))
	fmt.Printf("%v\n", tsTZ.ToDate(ctx))
	fmt.Printf("%v\n", tsTZ.ToTime(ctx))

	tz, err = time.LoadLocation("America/Los_Angeles")
	if err != nil {
		log.Fatal(err)
	}
	ctx = types.ContextWithTZ(context.Background(), tz)
	fmt.Printf("%v\n", tsTZ.ToTimestamp(ctx))
	fmt.Printf("%v\n", tsTZ.ToDate(ctx))
	fmt.Printf("%v\n", tsTZ.ToTime(ctx))
}

Output:

2023-08-15T12:34:56-04:00
2023-08-15T16:34:56
2023-08-15
16:34:56
2023-08-15T09:34:56
2023-08-15
09:34:56

func NewTimestampTZ 

func NewTimestampTZ(ctx context.Context, src time.Time) *TimestampTZ

NewTimestampTZ creates a timestamp with time zone with src. The ctx param is used solely to determine the time zone used by TimestampTZ.String.

func (*TimestampTZ) Compare 

func (ts *TimestampTZ) Compare(u time.Time) int

Compare compares the time instant ts with u. If ts is before u, it returns -1; if ts is after u, it returns +1; if they're the same, it returns 0.

func (*TimestampTZ) GoTime 

func (ts *TimestampTZ) GoTime() time.Time

GoTime returns the underlying time.Time object.

func (*TimestampTZ) MarshalJSON 

func (ts *TimestampTZ) MarshalJSON() ([]byte, error)

MarshalJSON implements the json.Marshaler interface. The time is a quoted string using the "2006-01-02T15:04:05.999999999-07:00" format.

func (*TimestampTZ) String 

func (ts *TimestampTZ) String() string

String returns the string representation of ts in the time zone in the Context passed to NewTimestampTZ, using the format "2006-01-02T15:04:05.999999999-07:00".

func (*TimestampTZ) ToDate 

func (ts *TimestampTZ) ToDate(ctx context.Context) *Date

ToDate converts ts to *Date in the time zone in ctx.

func (*TimestampTZ) ToTime 

func (ts *TimestampTZ) ToTime(ctx context.Context) *Time

ToTime converts ts to *Time in the time zone in ctx.

func (*TimestampTZ) ToTimeTZ 

func (ts *TimestampTZ) ToTimeTZ(ctx context.Context) *TimeTZ

ToTimeTZ converts ts to TimeTZ in the time zone in ctx.

func (*TimestampTZ) ToTimestamp 

func (ts *TimestampTZ) ToTimestamp(ctx context.Context) *Timestamp

ToTimestamp converts ts to *Timestamp in the time zone in ctx.

func (*TimestampTZ) UnmarshalJSON 

func (ts *TimestampTZ) UnmarshalJSON(data []byte) error

UnmarshalJSON implements the json.Unmarshaler interface. The time must be a quoted string in one of the following formats:

  • 2006-01-02T15:04:05.999999999Z07:00:00
  • 2006-01-02T15:04:05.999999999Z07:00
  • 2006-01-02T15:04:05.999999999Z07

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.