The TO_TIMESTAMP in Postgresql

2.1 Oracle

2.1.1 Types

  • DATE
    • It alwarys has year, month, day, hour, minute, second. no timezone
    • produce with to_date, TZ won’t be understood and error will be raised
    • produce with cast(.. as DATE), TZ part will be ignored
    • similar to postgresql’s TIMESTAMP WITHOUT TIME ZONE
  • TIMESTAMP(TIMESTAMP WITHOUT TIME ZONE)
    • no timezone, literal datetime. similar to postgresql’s TIMESTAMP WITHOUT TIME ZONE. CAST will ignore the TZ part only preserve the datetime part.
  • TSWTZ(TIMSTAMP WITH TIME ZONE)
    • datetime + seperate TZ label
  • TSLTZ(TIMESTAMP WITH LOCAL TIME ZONE)
    • converts datetimestring with specified TZ or session TZ to timestamp at UTC, displays in session TZ. similar to TIMSTAMP WITH TIME ZONE in postgresql

2.1.2 Parsing

  • to_timestamp_tz()

    • if no datatime formate template is provided as second parameter,

      • NLS_TIMESTAMP_TZ_FORMAT will be used.
      • if the NLS_TIMESTAMP_TZ_FORMAT is not specified, a datetime format derived from NLS_TERRITORY will be used. e.g., when NLS_TERRITORY=AMERICA, a common session default is “DD-MON-RR HH.MI.SSXFF AM TZR” will be used.
      • the datetime format from NLS_TIMESTAMP_TZ_FORMAT and derived from NLS_TERRIORY are not stable, which is not able to be guessed or assumed supported by PG’s timestamp casting.

    • It produce a timestamp with time zone

  • to_timestamp()

    • same to to_timestamp_tz(), when no format is provided as second parameter, oracle determine the format to be use from

      • NLS_TIMESTAMP_FORMAT
      • NLS_TERRITORY
        this will be not stable, as user may use the default format, may use customized format, and also the format from JDBC client.

    • it produce a timestamp without time zone

  • to_date()

2.2 Postgresql

2.2.1 Types

  • DATE

    • different from oracle’s DATE type, only date is supported.

  • TIMESTAMP(TIMESTAMP WITHOUT TIME ZONE)

    • euqivalent to Oracle’s TIMESTAMP

  • TIMESTAMPTZ(TIMESTAMP WITH TIME ZONE)

    • equivalent to Oracle’s TSLTZ(TIMESTAMP WITH LOCAL TIME ZONE)

  • no equivalents to oracle’s TSWTZ(TIMESTAMP WITH TIME ZONE)

2.2.2 Parsing

  • TIMESTAMP expression

  • TIMESTAMPTZ expression

  • CAST(… as TIMESTAMP)

  • CAST(… as TIMESTAMPTZ)

    • The rule of how PG detects tokens (dates, times, bumeric/alpha time-zone tokens)
    • In postgresql’s documentation, it says
      “to_timestamp() and to_date() exist to handle input formats that cannot be converted by simple casting. For most standard date/time formats, simply casting the source string to the required data type works,a nd is much easier. Similarly, to_number is unnecessary for standard numeric representations.”

  • to_timestamp()

    • tzm and tzh were supported since PG11, but TZ was supported until PG17. For older PG when an TZ part is provided in the datetime string, error would be raised to say it doesn’t recognize it. But the function do produce a TIMESTAMPTZ type. the timezone to be used is session timezone, the stored timstampe is calcualted UTC timestamp.
    • From the postgresql 17, to_timestamp() start to support most timezone part in datetime string and format. evolution about the TIME ZONE parsing support
      • pre-v10: none
      • v11-v16: tzm tzh
      • v17 + : tzm tzh tz OF

  • AT TIME ZONE

    • timestamp AT TIME ZONE zone -> timestamptz
    • timestamptz AT TIME ZONE zone -> timestamp
TIMEZONE PART Description Example Supported By PG’s to_timestamp() Since Oracle
TZH Time Zone Hour -07 v11 yes
TZM Time Zone Minute 30 v11 yes
TZ Time Zone Abbrevation UTC v17 no
TZR Time Zone Region US/Pacific not yet yes
TZD* DST* Abbreviation PDT not yet yes
OF Time Zone Offset from UTC +05:30 v17 TZH:TZM
  • Oracle requires using TZD together with TZR to disambiguate abbreviations across regions, but Postgresql’s timestamptz casts don’t allow both to be supplied at the same time.
  • DST- Daylight Saving Time