Materialize/test/sqllogictest/postgres/timestamp.slt

# Copyright 1994, Regents of the University of California.
# Copyright 1996-2019 PostgreSQL Global Development Group.
# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.
#
# This file is derived from the regression test suite in PostgreSQL.
# The original file was retrieved on November 27, 2019 from:
#
#     https://github.com/postgres/postgres/blob/290acac92b1d7bebb4ec68fe8b7a5cb442333eda/src/test/regress/expected/timestamp.out
#
# The original source code is subject to the terms of the PostgreSQL
# license, a copy of which can be found in the LICENSE file at the
# root of this repository.

statement ok
CREATE TABLE t (a timestamp without time zone);

# Postgres v6.0 standard output format
statement ok
INSERT INTO t VALUES ('Mon Feb 10 17:32:01 1997 PST')

# Variations on Postgres v6.1 standard output format
statement ok
INSERT INTO t VALUES
    ('Mon Feb 10 17:32:01.000001 1997 PST'),
    ('Mon Feb 10 17:32:01.999999 1997 PST'),
    ('Mon Feb 10 17:32:01.4 1997 PST'),
    ('Mon Feb 10 17:32:01.5 1997 PST'),
    ('Mon Feb 10 17:32:01.6 1997 PST')

# ISO 8601 format
statement ok
INSERT INTO t VALUES
    ('1997-01-02'),
    ('1997-01-02 03:04:05'),
    ('1997-02-10 17:32:01-08'),
    ('1997-02-10 17:32:01-0800'),
    ('1997-02-10 17:32:01 -08:00'),
    ('19970210 173201 -0800'),
    ('1997-06-10 17:32:01 -07:00'),
    ('2001-09-22T18:19:20')

# POSIX format (note that the timezone abbrev is just decoration here)
statement ok
INSERT INTO t VALUES
    ('2000-03-15 08:14:01 GMT+8'),
    ('2000-03-15 13:14:02 GMT-1'),
    ('2000-03-15 12:14:03 GMT-2'),
    ('2000-03-15 03:14:04 PST+8'),
    ('2000-03-15 02:14:05 MST+7:00')

# Variations for acceptable input formats
statement ok
INSERT INTO t VALUES
    ('Feb 10 17:32:01 1997 -0800'),
    ('Feb 10 17:32:01 1997'),
    ('Feb 10 5:32PM 1997'),
    ('1997/02/10 17:32:01-0800'),
    ('1997-02-10 17:32:01 PST'),
    ('Feb-10-1997 17:32:01 PST'),
    ('02-10-1997 17:32:01 PST'),
    ('19970210 173201 PST')

statement ok
INSERT INTO t VALUES ('1997.041 17:32:01 UTC'), ('19970210 173201 America/New_York')

# this fails (even though TZ is a no-op, we still look it up)
statement error time zone "america/does_not_exist" not recognized
INSERT INTO t VALUES ('19970710 173201 America/Does_not_exist')

# Check date conversion and date arithmetic
statement ok
INSERT INTO t VALUES
    ('1997-06-10 18:32:01 PDT'),
    ('Feb 10 17:32:01 1997'),
    ('Feb 11 17:32:01 1997'),
    ('Feb 12 17:32:01 1997'),
    ('Feb 13 17:32:01 1997'),
    ('Feb 14 17:32:01 1997'),
    ('Feb 15 17:32:01 1997'),
    ('Feb 16 17:32:01 1997'),
    ('Feb 16 17:32:01 0097 BC'),
    ('Feb 16 17:32:01 0097'),
    ('Feb 16 17:32:01 0597'),
    ('Feb 16 17:32:01 1097'),
    ('Feb 16 17:32:01 1697'),
    ('Feb 16 17:32:01 1797'),
    ('Feb 16 17:32:01 1897'),
    ('Feb 16 17:32:01 1997'),
    ('Feb 16 17:32:01 2097'),
    ('Feb 28 17:32:01 1996'),
    ('Feb 29 17:32:01 1996'),
    ('Mar 01 17:32:01 1996'),
    ('Dec 30 17:32:01 1996'),
    ('Dec 31 17:32:01 1996'),
    ('Jan 01 17:32:01 1997'),
    ('Feb 28 17:32:01 1997')

statement error date/time field value out of range: "Feb 29 17:32:01 1997"
INSERT INTO t VALUES ('Feb 29 17:32:01 1997')

statement ok
INSERT INTO t VALUES
    ('Mar 01 17:32:01 1997'),
    ('Dec 30 17:32:01 1997'),
    ('Dec 31 17:32:01 1997'),
    ('Dec 31 17:32:01 1999'),
    ('Jan 01 17:32:01 2000'),
    ('Dec 31 17:32:01 2000'),
    ('Jan 01 17:32:01 2001')

# Currently unsupported syntax and ranges
statement error time zone displacement out of range: "Feb 16 17:32:01 -0097"
INSERT INTO t VALUES ('Feb 16 17:32:01 -0097')

statement error timestamp out of range: "Feb 16 17:32:01 5097 BC"
INSERT INTO t VALUES ('Feb 16 17:32:01 5097 BC')

query T rowsort
SELECT a FROM t
----
-0096-02-16 17:32:01
0097-02-16 17:32:01
0597-02-16 17:32:01
1097-02-16 17:32:01
1697-02-16 17:32:01
1797-02-16 17:32:01
1897-02-16 17:32:01
1996-02-28 17:32:01
1996-02-29 17:32:01
1996-03-01 17:32:01
1996-12-30 17:32:01
1996-12-31 17:32:01
1997-01-01 17:32:01
1997-01-02 00:00:00
1997-01-02 03:04:05
1997-02-10 17:32:00
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01.000001
1997-02-10 17:32:01.400
1997-02-10 17:32:01.500
1997-02-10 17:32:01.600
1997-02-10 17:32:01.999999
1997-02-11 17:32:01
1997-02-12 17:32:01
1997-02-13 17:32:01
1997-02-14 17:32:01
1997-02-15 17:32:01
1997-02-16 17:32:01
1997-02-16 17:32:01
1997-02-28 17:32:01
1997-03-01 17:32:01
1997-06-10 17:32:01
1997-06-10 18:32:01
1997-12-30 17:32:01
1997-12-31 17:32:01
1999-12-31 17:32:01
2000-01-01 17:32:01
2000-03-15 02:14:05
2000-03-15 03:14:04
2000-03-15 08:14:01
2000-03-15 12:14:03
2000-03-15 13:14:02
2000-12-31 17:32:01
2001-01-01 17:32:01
2001-09-22 18:19:20
2097-02-16 17:32:01

# Check behavior at the lower boundary of the timestamp range
query T
SELECT '4714-11-24 00:00:00 BC'::timestamp
----
Mon Nov 24 00:00:00 4714 BC

query error timestamp out of range: "4714-11-23 23:59:59 BC"
SELECT '4714-11-23 23:59:59 BC'::timestamp  -- out of range

# The upper boundary differs between integer and float timestamps, so no check
# Demonstrate functions and operators
query T rowsort
SELECT a FROM t WHERE a > timestamp without time zone '1997-01-02'
----
1997-01-02 03:04:05
1997-02-10 17:32:00
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01.000001
1997-02-10 17:32:01.400
1997-02-10 17:32:01.500
1997-02-10 17:32:01.600
1997-02-10 17:32:01.999999
1997-02-11 17:32:01
1997-02-12 17:32:01
1997-02-13 17:32:01
1997-02-14 17:32:01
1997-02-15 17:32:01
1997-02-16 17:32:01
1997-02-16 17:32:01
1997-02-28 17:32:01
1997-03-01 17:32:01
1997-06-10 17:32:01
1997-06-10 18:32:01
1997-12-30 17:32:01
1997-12-31 17:32:01
1999-12-31 17:32:01
2000-01-01 17:32:01
2000-03-15 02:14:05
2000-03-15 03:14:04
2000-03-15 08:14:01
2000-03-15 12:14:03
2000-03-15 13:14:02
2000-12-31 17:32:01
2001-01-01 17:32:01
2001-09-22 18:19:20
2097-02-16 17:32:01

query T rowsort
SELECT a FROM t WHERE a < timestamp without time zone '1997-01-02'
----
-0096-02-16 17:32:01
0097-02-16 17:32:01
0597-02-16 17:32:01
1097-02-16 17:32:01
1697-02-16 17:32:01
1797-02-16 17:32:01
1897-02-16 17:32:01
1996-02-28 17:32:01
1996-02-29 17:32:01
1996-03-01 17:32:01
1996-12-30 17:32:01
1996-12-31 17:32:01
1997-01-01 17:32:01

query T
SELECT a FROM t WHERE a = timestamp without time zone '1997-01-02'
----
1997-01-02 00:00:00

query T rowsort
SELECT a FROM t WHERE a != timestamp without time zone '1997-01-02'
----
-0096-02-16 17:32:01
0097-02-16 17:32:01
0597-02-16 17:32:01
1097-02-16 17:32:01
1697-02-16 17:32:01
1797-02-16 17:32:01
1897-02-16 17:32:01
1996-02-28 17:32:01
1996-02-29 17:32:01
1996-03-01 17:32:01
1996-12-30 17:32:01
1996-12-31 17:32:01
1997-01-01 17:32:01
1997-01-02 03:04:05
1997-02-10 17:32:00
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01.000001
1997-02-10 17:32:01.400
1997-02-10 17:32:01.500
1997-02-10 17:32:01.600
1997-02-10 17:32:01.999999
1997-02-11 17:32:01
1997-02-12 17:32:01
1997-02-13 17:32:01
1997-02-14 17:32:01
1997-02-15 17:32:01
1997-02-16 17:32:01
1997-02-16 17:32:01
1997-02-28 17:32:01
1997-03-01 17:32:01
1997-06-10 17:32:01
1997-06-10 18:32:01
1997-12-30 17:32:01
1997-12-31 17:32:01
1999-12-31 17:32:01
2000-01-01 17:32:01
2000-03-15 02:14:05
2000-03-15 03:14:04
2000-03-15 08:14:01
2000-03-15 12:14:03
2000-03-15 13:14:02
2000-12-31 17:32:01
2001-01-01 17:32:01
2001-09-22 18:19:20
2097-02-16 17:32:01

query T rowsort
SELECT a FROM t WHERE a <= timestamp without time zone '1997-01-02'
----
-0096-02-16 17:32:01
0097-02-16 17:32:01
0597-02-16 17:32:01
1097-02-16 17:32:01
1697-02-16 17:32:01
1797-02-16 17:32:01
1897-02-16 17:32:01
1996-02-28 17:32:01
1996-02-29 17:32:01
1996-03-01 17:32:01
1996-12-30 17:32:01
1996-12-31 17:32:01
1997-01-01 17:32:01
1997-01-02 00:00:00

query T rowsort
SELECT a FROM t WHERE a >= timestamp without time zone '1997-01-02'
----
1997-01-02 00:00:00
1997-01-02 03:04:05
1997-02-10 17:32:00
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01
1997-02-10 17:32:01.000001
1997-02-10 17:32:01.400
1997-02-10 17:32:01.500
1997-02-10 17:32:01.600
1997-02-10 17:32:01.999999
1997-02-11 17:32:01
1997-02-12 17:32:01
1997-02-13 17:32:01
1997-02-14 17:32:01
1997-02-15 17:32:01
1997-02-16 17:32:01
1997-02-16 17:32:01
1997-02-28 17:32:01
1997-03-01 17:32:01
1997-06-10 17:32:01
1997-06-10 18:32:01
1997-12-30 17:32:01
1997-12-31 17:32:01
1999-12-31 17:32:01
2000-01-01 17:32:01
2000-03-15 02:14:05
2000-03-15 03:14:04
2000-03-15 08:14:01
2000-03-15 12:14:03
2000-03-15 13:14:02
2000-12-31 17:32:01
2001-01-01 17:32:01
2001-09-22 18:19:20
2097-02-16 17:32:01

query T rowsort
SELECT a - timestamp without time zone '1997-01-02' AS diff
FROM t WHERE a BETWEEN '1902-01-01' AND '2038-01-01'
----
-06:27:59
-1 days -06:27:59
-2 days -06:27:59
-306 days -06:27:59
-307 days -06:27:59
-308 days -06:27:59
00:00:00
03:04:05
1093 days 17:32:01
1094 days 17:32:01
1168 days 02:14:05
1168 days 03:14:04
1168 days 08:14:01
1168 days 12:14:03
1168 days 13:14:02
1459 days 17:32:01
1460 days 17:32:01
159 days 17:32:01
159 days 18:32:01
1724 days 18:19:20
362 days 17:32:01
363 days 17:32:01
39 days 17:32:00
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01.000001
39 days 17:32:01.4
39 days 17:32:01.5
39 days 17:32:01.6
39 days 17:32:01.999999
40 days 17:32:01
41 days 17:32:01
42 days 17:32:01
43 days 17:32:01
44 days 17:32:01
45 days 17:32:01
45 days 17:32:01
57 days 17:32:01
58 days 17:32:01

query T
SELECT date_trunc('week', timestamp '2004-02-29 15:44:17.71393') AS week_trunc
----
2004-02-23 00:00:00

# Test casting within a BETWEEN qualifier
query T rowsort
SELECT a - timestamp without time zone '1997-01-02' AS diff
FROM t
WHERE a BETWEEN timestamp without time zone '1902-01-01' AND timestamp without time zone '2038-01-01'
----
-06:27:59
-1 days -06:27:59
-2 days -06:27:59
-306 days -06:27:59
-307 days -06:27:59
-308 days -06:27:59
00:00:00
03:04:05
1093 days 17:32:01
1094 days 17:32:01
1168 days 02:14:05
1168 days 03:14:04
1168 days 08:14:01
1168 days 12:14:03
1168 days 13:14:02
1459 days 17:32:01
1460 days 17:32:01
159 days 17:32:01
159 days 18:32:01
1724 days 18:19:20
362 days 17:32:01
363 days 17:32:01
39 days 17:32:00
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01
39 days 17:32:01.000001
39 days 17:32:01.4
39 days 17:32:01.5
39 days 17:32:01.6
39 days 17:32:01.999999
40 days 17:32:01
41 days 17:32:01
42 days 17:32:01
43 days 17:32:01
44 days 17:32:01
45 days 17:32:01
45 days 17:32:01
57 days 17:32:01
58 days 17:32:01

mode cockroach

query T
SELECT
  a as "timestamp",
  date_part('year', a) AS year, date_part('month', a) AS month,
  date_part('day', a) AS day, date_part('hour', a) AS hour,
  date_part('minute', a) AS minute, date_part('second', a) AS second
FROM t WHERE a BETWEEN '1902-01-01' AND '2038-01-01'
----
Thu Jan 01 00:00:00 1970     1970       1     1      0        0        0
Mon Feb 10 17:32:01 1997     1997       2    10     17       32        1
Mon Feb 10 17:32:01 1997     1997       2    10     17       32        1
Mon Feb 10 17:32:02 1997     1997       2    10     17       32        2
Mon Feb 10 17:32:01.4 1997   1997       2    10     17       32      1.4
Mon Feb 10 17:32:01.5 1997   1997       2    10     17       32      1.5
Mon Feb 10 17:32:01.6 1997   1997       2    10     17       32      1.6
Thu Jan 02 00:00:00 1997     1997       1     2      0        0        0
Thu Jan 02 03:04:05 1997     1997       1     2      3        4        5
Mon Feb 10 17:32:01 1997     1997       2    10     17       32        1
Mon Feb 10 17:32:01 1997     1997       2    10     17       32        1
Mon Feb 10 17:32:01 1997     1997       2    10     17       32        1
Mon Feb 10 17:32:01 1997     1997       2    10     17       32        1
Tue Jun 10 17:32:01 1997     1997       6    10     17       32        1
Sat Sep 22 18:19:20 2001     2001       9    22     18       19       20
Wed Mar 15 08:14:01 2000     2000       3    15      8       14        1
Wed Mar 15 13:14:02 2000     2000       3    15     13       14        2
Wed Mar 15 12:14:03 2000     2000       3    15     12       14        3
Wed Mar 15 03:14:04 2000     2000       3    15      3       14        4
Wed Mar 15 02:14:05 2000     2000       3    15      2       14        5
Mon Feb 10 17:32:01 1997     1997       2    10     17       32        1
Mon Feb 10 17:32:01 1997     1997       2    10     17       32        1
Mon Feb 10 17:32:00 1997     1997       2    10     17       32        0
Mon Feb 10 17:32:01 1997     1997       2    10     17       32        1
Mon Feb 10 17:32:01 1997     1997       2    10     17       32        1
Mon Feb 10 17:32:01 1997     1997       2    10     17       32        1
Mon Feb 10 17:32:01 1997     1997       2    10     17       32        1
Mon Feb 10 17:32:01 1997     1997       2    10     17       32        1
Mon Feb 10 17:32:01 1997     1997       2    10     17       32        1
Mon Feb 10 17:32:01 1997     1997       2    10     17       32        1
Mon Feb 10 17:32:01 1997     1997       2    10     17       32        1
Mon Feb 10 17:32:01 1997     1997       2    10     17       32        1
Tue Jun 10 18:32:01 1997     1997       6    10     18       32        1
Mon Feb 10 17:32:01 1997     1997       2    10     17       32        1
Tue Feb 11 17:32:01 1997     1997       2    11     17       32        1
Wed Feb 12 17:32:01 1997     1997       2    12     17       32        1
Thu Feb 13 17:32:01 1997     1997       2    13     17       32        1
Fri Feb 14 17:32:01 1997     1997       2    14     17       32        1
Sat Feb 15 17:32:01 1997     1997       2    15     17       32        1
Sun Feb 16 17:32:01 1997     1997       2    16     17       32        1
Sun Feb 16 17:32:01 1997     1997       2    16     17       32        1
Wed Feb 28 17:32:01 1996     1996       2    28     17       32        1
Thu Feb 29 17:32:01 1996     1996       2    29     17       32        1
Fri Mar 01 17:32:01 1996     1996       3     1     17       32        1
Mon Dec 30 17:32:01 1996     1996      12    30     17       32        1
Tue Dec 31 17:32:01 1996     1996      12    31     17       32        1
Wed Jan 01 17:32:01 1997     1997       1     1     17       32        1
Fri Feb 28 17:32:01 1997     1997       2    28     17       32        1
Sat Mar 01 17:32:01 1997     1997       3     1     17       32        1
Tue Dec 30 17:32:01 1997     1997      12    30     17       32        1
Wed Dec 31 17:32:01 1997     1997      12    31     17       32        1
Fri Dec 31 17:32:01 1999     1999      12    31     17       32        1
Sat Jan 01 17:32:01 2000     2000       1     1     17       32        1
Sun Dec 31 17:32:01 2000     2000      12    31     17       32        1
Mon Jan 01 17:32:01 2001     2001       1     1     17       32        1


query T
SELECT
  a as "timestamp",
  date_part('quarter', a) AS quarter,
  date_part('msec', a) AS msec,
  date_part('usec', a) AS usec
FROM t WHERE a BETWEEN '1902-01-01' AND '2038-01-01'
----
Thu Jan 01 00:00:00 1970           1       0          0
Mon Feb 10 17:32:01 1997           1    1000    1000000
Mon Feb 10 17:32:01 1997           1    1000    1000000
Mon Feb 10 17:32:02 1997           1    2000    2000000
Mon Feb 10 17:32:01.4 1997         1    1400    1400000
Mon Feb 10 17:32:01.5 1997         1    1500    1500000
Mon Feb 10 17:32:01.6 1997         1    1600    1600000
Thu Jan 02 00:00:00 1997           1       0          0
Thu Jan 02 03:04:05 1997           1    5000    5000000
Mon Feb 10 17:32:01 1997           1    1000    1000000
Mon Feb 10 17:32:01 1997           1    1000    1000000
Mon Feb 10 17:32:01 1997           1    1000    1000000
Mon Feb 10 17:32:01 1997           1    1000    1000000
Tue Jun 10 17:32:01 1997           2    1000    1000000
Sat Sep 22 18:19:20 2001           3   20000   20000000
Wed Mar 15 08:14:01 2000           1    1000    1000000
Wed Mar 15 13:14:02 2000           1    2000    2000000
Wed Mar 15 12:14:03 2000           1    3000    3000000
Wed Mar 15 03:14:04 2000           1    4000    4000000
Wed Mar 15 02:14:05 2000           1    5000    5000000
Mon Feb 10 17:32:01 1997           1    1000    1000000
Mon Feb 10 17:32:01 1997           1    1000    1000000
Mon Feb 10 17:32:00 1997           1       0          0
Mon Feb 10 17:32:01 1997           1    1000    1000000
Mon Feb 10 17:32:01 1997           1    1000    1000000
Mon Feb 10 17:32:01 1997           1    1000    1000000
Mon Feb 10 17:32:01 1997           1    1000    1000000
Mon Feb 10 17:32:01 1997           1    1000    1000000
Mon Feb 10 17:32:01 1997           1    1000    1000000
Mon Feb 10 17:32:01 1997           1    1000    1000000
Mon Feb 10 17:32:01 1997           1    1000    1000000
Mon Feb 10 17:32:01 1997           1    1000    1000000
Tue Jun 10 18:32:01 1997           2    1000    1000000
Mon Feb 10 17:32:01 1997           1    1000    1000000
Tue Feb 11 17:32:01 1997           1    1000    1000000
Wed Feb 12 17:32:01 1997           1    1000    1000000
Thu Feb 13 17:32:01 1997           1    1000    1000000
Fri Feb 14 17:32:01 1997           1    1000    1000000
Sat Feb 15 17:32:01 1997           1    1000    1000000
Sun Feb 16 17:32:01 1997           1    1000    1000000
Sun Feb 16 17:32:01 1997           1    1000    1000000
Wed Feb 28 17:32:01 1996           1    1000    1000000
Thu Feb 29 17:32:01 1996           1    1000    1000000
Fri Mar 01 17:32:01 1996           1    1000    1000000
Mon Dec 30 17:32:01 1996           4    1000    1000000
Tue Dec 31 17:32:01 1996           4    1000    1000000
Wed Jan 01 17:32:01 1997           1    1000    1000000
Fri Feb 28 17:32:01 1997           1    1000    1000000
Sat Mar 01 17:32:01 1997           1    1000    1000000
Tue Dec 30 17:32:01 1997           4    1000    1000000
Wed Dec 31 17:32:01 1997           4    1000    1000000
Fri Dec 31 17:32:01 1999           4    1000    1000000
Sat Jan 01 17:32:01 2000           1    1000    1000000
Sun Dec 31 17:32:01 2000           4    1000    1000000
Mon Jan 01 17:32:01 2001           1    1000    1000000

query T
SELECT
  a as "timestamp",
  date_part('isoyear', a) AS isoyear, date_part('week', a) AS week,
  date_part('dow', a) AS dow
FROM t WHERE a BETWEEN '1902-01-01' AND '2038-01-01'
----
Thu Jan 01 00:00:00 1970        1970      1     4
Mon Feb 10 17:32:01 1997        1997      7     1
Mon Feb 10 17:32:01 1997        1997      7     1
Mon Feb 10 17:32:02 1997        1997      7     1
Mon Feb 10 17:32:01.4 1997      1997      7     1
Mon Feb 10 17:32:01.5 1997      1997      7     1
Mon Feb 10 17:32:01.6 1997      1997      7     1
Thu Jan 02 00:00:00 1997        1997      1     4
Thu Jan 02 03:04:05 1997        1997      1     4
Mon Feb 10 17:32:01 1997        1997      7     1
Mon Feb 10 17:32:01 1997        1997      7     1
Mon Feb 10 17:32:01 1997        1997      7     1
Mon Feb 10 17:32:01 1997        1997      7     1
Tue Jun 10 17:32:01 1997        1997     24     2
Sat Sep 22 18:19:20 2001        2001     38     6
Wed Mar 15 08:14:01 2000        2000     11     3
Wed Mar 15 13:14:02 2000        2000     11     3
Wed Mar 15 12:14:03 2000        2000     11     3
Wed Mar 15 03:14:04 2000        2000     11     3
Wed Mar 15 02:14:05 2000        2000     11     3
Mon Feb 10 17:32:01 1997        1997      7     1
Mon Feb 10 17:32:01 1997        1997      7     1
Mon Feb 10 17:32:00 1997        1997      7     1
Mon Feb 10 17:32:01 1997        1997      7     1
Mon Feb 10 17:32:01 1997        1997      7     1
Mon Feb 10 17:32:01 1997        1997      7     1
Mon Feb 10 17:32:01 1997        1997      7     1
Mon Feb 10 17:32:01 1997        1997      7     1
Mon Feb 10 17:32:01 1997        1997      7     1
Mon Feb 10 17:32:01 1997        1997      7     1
Mon Feb 10 17:32:01 1997        1997      7     1
Mon Feb 10 17:32:01 1997        1997      7     1
Tue Jun 10 18:32:01 1997        1997     24     2
Mon Feb 10 17:32:01 1997        1997      7     1
Tue Feb 11 17:32:01 1997        1997      7     2
Wed Feb 12 17:32:01 1997        1997      7     3
Thu Feb 13 17:32:01 1997        1997      7     4
Fri Feb 14 17:32:01 1997        1997      7     5
Sat Feb 15 17:32:01 1997        1997      7     6
Sun Feb 16 17:32:01 1997        1997      7     0
Sun Feb 16 17:32:01 1997        1997      7     0
Wed Feb 28 17:32:01 1996        1996      9     3
Thu Feb 29 17:32:01 1996        1996      9     4
Fri Mar 01 17:32:01 1996        1996      9     5
Mon Dec 30 17:32:01 1996        1997      1     1
Tue Dec 31 17:32:01 1996        1997      1     2
Wed Jan 01 17:32:01 1997        1997      1     3
Fri Feb 28 17:32:01 1997        1997      9     5
Sat Mar 01 17:32:01 1997        1997      9     6
Tue Dec 30 17:32:01 1997        1998      1     2
Wed Dec 31 17:32:01 1997        1998      1     3
Fri Dec 31 17:32:01 1999        1999     52     5
Sat Jan 01 17:32:01 2000        1999     52     6
Sun Dec 31 17:32:01 2000        2000     52     0
Mon Jan 01 17:32:01 2001        2001      1     1

query T rowsort
SELECT to_char(a, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon') FROM t
----
FRIDAY    Friday    friday    FRI Fri fri DECEMBER  December  december  XII  DEC Dec dec
FRIDAY    Friday    friday    FRI Fri fri FEBRUARY  February  february  II   FEB Feb feb
FRIDAY    Friday    friday    FRI Fri fri FEBRUARY  February  february  II   FEB Feb feb
FRIDAY    Friday    friday    FRI Fri fri MARCH     March     march     III  MAR Mar mar
MONDAY    Monday    monday    MON Mon mon DECEMBER  December  december  XII  DEC Dec dec
MONDAY    Monday    monday    MON Mon mon FEBRUARY  February  february  II   FEB Feb feb
MONDAY    Monday    monday    MON Mon mon FEBRUARY  February  february  II   FEB Feb feb
MONDAY    Monday    monday    MON Mon mon FEBRUARY  February  february  II   FEB Feb feb
MONDAY    Monday    monday    MON Mon mon FEBRUARY  February  february  II   FEB Feb feb
MONDAY    Monday    monday    MON Mon mon FEBRUARY  February  february  II   FEB Feb feb
MONDAY    Monday    monday    MON Mon mon FEBRUARY  February  february  II   FEB Feb feb
MONDAY    Monday    monday    MON Mon mon FEBRUARY  February  february  II   FEB Feb feb
MONDAY    Monday    monday    MON Mon mon FEBRUARY  February  february  II   FEB Feb feb
MONDAY    Monday    monday    MON Mon mon FEBRUARY  February  february  II   FEB Feb feb
MONDAY    Monday    monday    MON Mon mon FEBRUARY  February  february  II   FEB Feb feb
MONDAY    Monday    monday    MON Mon mon FEBRUARY  February  february  II   FEB Feb feb
MONDAY    Monday    monday    MON Mon mon FEBRUARY  February  february  II   FEB Feb feb
MONDAY    Monday    monday    MON Mon mon FEBRUARY  February  february  II   FEB Feb feb
MONDAY    Monday    monday    MON Mon mon FEBRUARY  February  february  II   FEB Feb feb
MONDAY    Monday    monday    MON Mon mon FEBRUARY  February  february  II   FEB Feb feb
MONDAY    Monday    monday    MON Mon mon FEBRUARY  February  february  II   FEB Feb feb
MONDAY    Monday    monday    MON Mon mon FEBRUARY  February  february  II   FEB Feb feb
MONDAY    Monday    monday    MON Mon mon FEBRUARY  February  february  II   FEB Feb feb
MONDAY    Monday    monday    MON Mon mon FEBRUARY  February  february  II   FEB Feb feb
MONDAY    Monday    monday    MON Mon mon FEBRUARY  February  february  II   FEB Feb feb
MONDAY    Monday    monday    MON Mon mon FEBRUARY  February  february  II   FEB Feb feb
MONDAY    Monday    monday    MON Mon mon JANUARY   January   january   I    JAN Jan jan
SATURDAY  Saturday  saturday  SAT Sat sat FEBRUARY  February  february  II   FEB Feb feb
SATURDAY  Saturday  saturday  SAT Sat sat FEBRUARY  February  february  II   FEB Feb feb
SATURDAY  Saturday  saturday  SAT Sat sat FEBRUARY  February  february  II   FEB Feb feb
SATURDAY  Saturday  saturday  SAT Sat sat FEBRUARY  February  february  II   FEB Feb feb
SATURDAY  Saturday  saturday  SAT Sat sat JANUARY   January   january   I    JAN Jan jan
SATURDAY  Saturday  saturday  SAT Sat sat MARCH     March     march     III  MAR Mar mar
SATURDAY  Saturday  saturday  SAT Sat sat SEPTEMBER September september IX   SEP Sep sep
SUNDAY    Sunday    sunday    SUN Sun sun DECEMBER  December  december  XII  DEC Dec dec
SUNDAY    Sunday    sunday    SUN Sun sun FEBRUARY  February  february  II   FEB Feb feb
SUNDAY    Sunday    sunday    SUN Sun sun FEBRUARY  February  february  II   FEB Feb feb
THURSDAY  Thursday  thursday  THU Thu thu FEBRUARY  February  february  II   FEB Feb feb
THURSDAY  Thursday  thursday  THU Thu thu FEBRUARY  February  february  II   FEB Feb feb
THURSDAY  Thursday  thursday  THU Thu thu FEBRUARY  February  february  II   FEB Feb feb
THURSDAY  Thursday  thursday  THU Thu thu FEBRUARY  February  february  II   FEB Feb feb
THURSDAY  Thursday  thursday  THU Thu thu JANUARY   January   january   I    JAN Jan jan
THURSDAY  Thursday  thursday  THU Thu thu JANUARY   January   january   I    JAN Jan jan
TUESDAY   Tuesday   tuesday   TUE Tue tue DECEMBER  December  december  XII  DEC Dec dec
TUESDAY   Tuesday   tuesday   TUE Tue tue DECEMBER  December  december  XII  DEC Dec dec
TUESDAY   Tuesday   tuesday   TUE Tue tue FEBRUARY  February  february  II   FEB Feb feb
TUESDAY   Tuesday   tuesday   TUE Tue tue FEBRUARY  February  february  II   FEB Feb feb
TUESDAY   Tuesday   tuesday   TUE Tue tue FEBRUARY  February  february  II   FEB Feb feb
TUESDAY   Tuesday   tuesday   TUE Tue tue FEBRUARY  February  february  II   FEB Feb feb
TUESDAY   Tuesday   tuesday   TUE Tue tue JUNE      June      june      VI   JUN Jun jun
TUESDAY   Tuesday   tuesday   TUE Tue tue JUNE      June      june      VI   JUN Jun jun
WEDNESDAY Wednesday wednesday WED Wed wed DECEMBER  December  december  XII  DEC Dec dec
WEDNESDAY Wednesday wednesday WED Wed wed FEBRUARY  February  february  II   FEB Feb feb
WEDNESDAY Wednesday wednesday WED Wed wed FEBRUARY  February  february  II   FEB Feb feb
WEDNESDAY Wednesday wednesday WED Wed wed JANUARY   January   january   I    JAN Jan jan
WEDNESDAY Wednesday wednesday WED Wed wed MARCH     March     march     III  MAR Mar mar
WEDNESDAY Wednesday wednesday WED Wed wed MARCH     March     march     III  MAR Mar mar
WEDNESDAY Wednesday wednesday WED Wed wed MARCH     March     march     III  MAR Mar mar
WEDNESDAY Wednesday wednesday WED Wed wed MARCH     March     march     III  MAR Mar mar
WEDNESDAY Wednesday wednesday WED Wed wed MARCH     March     march     III  MAR Mar mar

query T rowsort
SELECT to_char(a, 'FMDAY FMDay FMday FMMONTH FMMonth FMmonth FMRM') FROM t
----
FRIDAY Friday friday DECEMBER December december XII
FRIDAY Friday friday FEBRUARY February february II
FRIDAY Friday friday FEBRUARY February february II
FRIDAY Friday friday MARCH March march III
MONDAY Monday monday DECEMBER December december XII
MONDAY Monday monday FEBRUARY February february II
MONDAY Monday monday FEBRUARY February february II
MONDAY Monday monday FEBRUARY February february II
MONDAY Monday monday FEBRUARY February february II
MONDAY Monday monday FEBRUARY February february II
MONDAY Monday monday FEBRUARY February february II
MONDAY Monday monday FEBRUARY February february II
MONDAY Monday monday FEBRUARY February february II
MONDAY Monday monday FEBRUARY February february II
MONDAY Monday monday FEBRUARY February february II
MONDAY Monday monday FEBRUARY February february II
MONDAY Monday monday FEBRUARY February february II
MONDAY Monday monday FEBRUARY February february II
MONDAY Monday monday FEBRUARY February february II
MONDAY Monday monday FEBRUARY February february II
MONDAY Monday monday FEBRUARY February february II
MONDAY Monday monday FEBRUARY February february II
MONDAY Monday monday FEBRUARY February february II
MONDAY Monday monday FEBRUARY February february II
MONDAY Monday monday FEBRUARY February february II
MONDAY Monday monday FEBRUARY February february II
MONDAY Monday monday JANUARY January january I
SATURDAY Saturday saturday FEBRUARY February february II
SATURDAY Saturday saturday FEBRUARY February february II
SATURDAY Saturday saturday FEBRUARY February february II
SATURDAY Saturday saturday FEBRUARY February february II
SATURDAY Saturday saturday JANUARY January january I
SATURDAY Saturday saturday MARCH March march III
SATURDAY Saturday saturday SEPTEMBER September september IX
SUNDAY Sunday sunday DECEMBER December december XII
SUNDAY Sunday sunday FEBRUARY February february II
SUNDAY Sunday sunday FEBRUARY February february II
THURSDAY Thursday thursday FEBRUARY February february II
THURSDAY Thursday thursday FEBRUARY February february II
THURSDAY Thursday thursday FEBRUARY February february II
THURSDAY Thursday thursday FEBRUARY February february II
THURSDAY Thursday thursday JANUARY January january I
THURSDAY Thursday thursday JANUARY January january I
TUESDAY Tuesday tuesday DECEMBER December december XII
TUESDAY Tuesday tuesday DECEMBER December december XII
TUESDAY Tuesday tuesday FEBRUARY February february II
TUESDAY Tuesday tuesday FEBRUARY February february II
TUESDAY Tuesday tuesday FEBRUARY February february II
TUESDAY Tuesday tuesday FEBRUARY February february II
TUESDAY Tuesday tuesday JUNE June june VI
TUESDAY Tuesday tuesday JUNE June june VI
WEDNESDAY Wednesday wednesday DECEMBER December december XII
WEDNESDAY Wednesday wednesday FEBRUARY February february II
WEDNESDAY Wednesday wednesday FEBRUARY February february II
WEDNESDAY Wednesday wednesday JANUARY January january I
WEDNESDAY Wednesday wednesday MARCH March march III
WEDNESDAY Wednesday wednesday MARCH March march III
WEDNESDAY Wednesday wednesday MARCH March march III
WEDNESDAY Wednesday wednesday MARCH March march III
WEDNESDAY Wednesday wednesday MARCH March march III

query T rowsort
SELECT to_char(a, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J') FROM t
----
0,097 0097 097 97 7 -01 1 02 07 047 16 3 1686042
0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536
0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157
1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778
1,697 1697 697 97 7 17 1 02 07 047 16 7 2340924
1,797 1797 797 97 7 18 1 02 07 047 16 5 2377448
1,897 1897 897 97 7 19 1 02 07 047 16 3 2413972
1,996 1996 996 96 6 20 1 02 09 059 28 4 2450142
1,996 1996 996 96 6 20 1 02 09 060 29 5 2450143
1,996 1996 996 96 6 20 1 03 09 061 01 6 2450144
1,996 1996 996 96 6 20 4 12 53 365 30 2 2450448
1,996 1996 996 96 6 20 4 12 53 366 31 3 2450449
1,997 1997 997 97 7 20 1 01 01 001 01 4 2450450
1,997 1997 997 97 7 20 1 01 01 002 02 5 2450451
1,997 1997 997 97 7 20 1 01 01 002 02 5 2450451
1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1,997 1997 997 97 7 20 1 02 06 042 11 3 2450491
1,997 1997 997 97 7 20 1 02 07 043 12 4 2450492
1,997 1997 997 97 7 20 1 02 07 044 13 5 2450493
1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
1,997 1997 997 97 7 20 1 02 09 059 28 6 2450508
1,997 1997 997 97 7 20 1 03 09 060 01 7 2450509
1,997 1997 997 97 7 20 2 06 23 161 10 3 2450610
1,997 1997 997 97 7 20 2 06 23 161 10 3 2450610
1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813
1,997 1997 997 97 7 20 4 12 53 365 31 4 2450814
1,999 1999 999 99 9 20 4 12 53 365 31 6 2451544
2,000 2000 000 00 0 20 1 01 01 001 01 7 2451545
2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
2,000 2000 000 00 0 20 4 12 53 366 31 1 2451910
2,001 2001 001 01 1 21 1 01 01 001 01 2 2451911
2,001 2001 001 01 1 21 3 09 38 265 22 7 2452175
2,097 2097 097 97 7 21 1 02 07 047 16 7 2487021

query T rowsort
SELECT to_char(a, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM FMWW FMDDD FMDD FMD FMJ') FROM t
----
0,097 97 97 97 7 -1 1 2 7 47 16 3 1686042
0,097 97 97 97 7 1 1 2 7 47 16 7 1756536
0,597 597 597 97 7 6 1 2 7 47 16 5 1939157
1,097 1097 97 97 7 11 1 2 7 47 16 3 2121778
1,697 1697 697 97 7 17 1 2 7 47 16 7 2340924
1,797 1797 797 97 7 18 1 2 7 47 16 5 2377448
1,897 1897 897 97 7 19 1 2 7 47 16 3 2413972
1,996 1996 996 96 6 20 1 2 9 59 28 4 2450142
1,996 1996 996 96 6 20 1 2 9 60 29 5 2450143
1,996 1996 996 96 6 20 1 3 9 61 1 6 2450144
1,996 1996 996 96 6 20 4 12 53 365 30 2 2450448
1,996 1996 996 96 6 20 4 12 53 366 31 3 2450449
1,997 1997 997 97 7 20 1 1 1 1 1 4 2450450
1,997 1997 997 97 7 20 1 1 1 2 2 5 2450451
1,997 1997 997 97 7 20 1 1 1 2 2 5 2450451
1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1,997 1997 997 97 7 20 1 2 6 42 11 3 2450491
1,997 1997 997 97 7 20 1 2 7 43 12 4 2450492
1,997 1997 997 97 7 20 1 2 7 44 13 5 2450493
1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494
1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495
1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
1,997 1997 997 97 7 20 1 2 9 59 28 6 2450508
1,997 1997 997 97 7 20 1 3 9 60 1 7 2450509
1,997 1997 997 97 7 20 2 6 23 161 10 3 2450610
1,997 1997 997 97 7 20 2 6 23 161 10 3 2450610
1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813
1,997 1997 997 97 7 20 4 12 53 365 31 4 2450814
1,999 1999 999 99 9 20 4 12 53 365 31 6 2451544
2,000 2000 0 0 0 20 1 1 1 1 1 7 2451545
2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
2,000 2000 0 0 0 20 4 12 53 366 31 1 2451910
2,001 2001 1 1 1 21 1 1 1 1 1 2 2451911
2,001 2001 1 1 1 21 3 9 38 265 22 7 2452175
2,097 2097 97 97 7 21 1 2 7 47 16 7 2487021

query T rowsort
SELECT to_char(a, 'HH HH12 HH24 MI SS SSSS') FROM t
----
01 01 13 14 02 47642
02 02 02 14 05 8045
03 03 03 04 05 11045
03 03 03 14 04 11644
05 05 17 32 00 63120
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
05 05 17 32 01 63121
06 06 18 19 20 65960
06 06 18 32 01 66721
08 08 08 14 01 29641
12 12 00 00 00 0
12 12 12 14 03 44043

query T rowsort
SELECT to_char(a, '"HH:MI:SS is" HH:MI:SS "\"text between quote marks\""') FROM t
----
HH:MI:SS is 01:14:02 "text between quote marks"
HH:MI:SS is 02:14:05 "text between quote marks"
HH:MI:SS is 03:04:05 "text between quote marks"
HH:MI:SS is 03:14:04 "text between quote marks"
HH:MI:SS is 05:32:00 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 05:32:01 "text between quote marks"
HH:MI:SS is 06:19:20 "text between quote marks"
HH:MI:SS is 06:32:01 "text between quote marks"
HH:MI:SS is 08:14:01 "text between quote marks"
HH:MI:SS is 12:00:00 "text between quote marks"
HH:MI:SS is 12:14:03 "text between quote marks"

query T rowsort
SELECT to_char(a, 'HH24--text--MI--text--SS') FROM t
----
00--text--00--text--00
02--text--14--text--05
03--text--04--text--05
03--text--14--text--04
08--text--14--text--01
12--text--14--text--03
13--text--14--text--02
17--text--32--text--00
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
17--text--32--text--01
18--text--19--text--20
18--text--32--text--01

query T rowsort
SELECT to_char(a, 'YYYYTH YYYYth Jth') FROM t
----
0097TH 0097th 1686042nd
0097TH 0097th 1756536th
0597TH 0597th 1939157th
1097TH 1097th 2121778th
1697TH 1697th 2340924th
1797TH 1797th 2377448th
1897TH 1897th 2413972nd
1996TH 1996th 2450142nd
1996TH 1996th 2450143rd
1996TH 1996th 2450144th
1996TH 1996th 2450448th
1996TH 1996th 2450449th
1997TH 1997th 2450450th
1997TH 1997th 2450451st
1997TH 1997th 2450451st
1997TH 1997th 2450490th
1997TH 1997th 2450490th
1997TH 1997th 2450490th
1997TH 1997th 2450490th
1997TH 1997th 2450490th
1997TH 1997th 2450490th
1997TH 1997th 2450490th
1997TH 1997th 2450490th
1997TH 1997th 2450490th
1997TH 1997th 2450490th
1997TH 1997th 2450490th
1997TH 1997th 2450490th
1997TH 1997th 2450490th
1997TH 1997th 2450490th
1997TH 1997th 2450490th
1997TH 1997th 2450490th
1997TH 1997th 2450490th
1997TH 1997th 2450490th
1997TH 1997th 2450490th
1997TH 1997th 2450490th
1997TH 1997th 2450490th
1997TH 1997th 2450491st
1997TH 1997th 2450492nd
1997TH 1997th 2450493rd
1997TH 1997th 2450494th
1997TH 1997th 2450495th
1997TH 1997th 2450496th
1997TH 1997th 2450496th
1997TH 1997th 2450508th
1997TH 1997th 2450509th
1997TH 1997th 2450610th
1997TH 1997th 2450610th
1997TH 1997th 2450813th
1997TH 1997th 2450814th
1999TH 1999th 2451544th
2000TH 2000th 2451545th
2000TH 2000th 2451619th
2000TH 2000th 2451619th
2000TH 2000th 2451619th
2000TH 2000th 2451619th
2000TH 2000th 2451619th
2000TH 2000th 2451910th
2001ST 2001st 2451911th
2001ST 2001st 2452175th
2097TH 2097th 2487021st

query T rowsort
SELECT to_char(a, 'YYYY A.D. YYYY a.d. YYYY bc HH:MI:SS P.M. HH:MI:SS p.m. HH:MI:SS pm') FROM t
----
0097 A.D. 0097 a.d. 0097 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
0097 B.C. 0097 b.c. 0097 bc 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
0597 A.D. 0597 a.d. 0597 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1097 A.D. 1097 a.d. 1097 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1697 A.D. 1697 a.d. 1697 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1797 A.D. 1797 a.d. 1797 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1897 A.D. 1897 a.d. 1897 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 03:04:05 A.M. 03:04:05 a.m. 03:04:05 am
1997 A.D. 1997 a.d. 1997 ad 05:32:00 P.M. 05:32:00 p.m. 05:32:00 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 06:32:01 P.M. 06:32:01 p.m. 06:32:01 pm
1997 A.D. 1997 a.d. 1997 ad 12:00:00 A.M. 12:00:00 a.m. 12:00:00 am
1999 A.D. 1999 a.d. 1999 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
2000 A.D. 2000 a.d. 2000 ad 01:14:02 P.M. 01:14:02 p.m. 01:14:02 pm
2000 A.D. 2000 a.d. 2000 ad 02:14:05 A.M. 02:14:05 a.m. 02:14:05 am
2000 A.D. 2000 a.d. 2000 ad 03:14:04 A.M. 03:14:04 a.m. 03:14:04 am
2000 A.D. 2000 a.d. 2000 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
2000 A.D. 2000 a.d. 2000 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
2000 A.D. 2000 a.d. 2000 ad 08:14:01 A.M. 08:14:01 a.m. 08:14:01 am
2000 A.D. 2000 a.d. 2000 ad 12:14:03 P.M. 12:14:03 p.m. 12:14:03 pm
2001 A.D. 2001 a.d. 2001 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
2001 A.D. 2001 a.d. 2001 ad 06:19:20 P.M. 06:19:20 p.m. 06:19:20 pm
2097 A.D. 2097 a.d. 2097 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm

query T rowsort
SELECT to_char(a, 'IYYY IYY IY I IW IDDD ID') FROM t
----
0097 097 97 7 07 044 2
0097 097 97 7 07 048 6
0597 597 97 7 07 046 4
1097 097 97 7 07 044 2
1697 697 97 7 07 048 6
1797 797 97 7 07 046 4
1897 897 97 7 07 044 2
1996 996 96 6 09 059 3
1996 996 96 6 09 060 4
1996 996 96 6 09 061 5
1997 997 97 7 01 001 1
1997 997 97 7 01 002 2
1997 997 97 7 01 003 3
1997 997 97 7 01 004 4
1997 997 97 7 01 004 4
1997 997 97 7 07 043 1
1997 997 97 7 07 043 1
1997 997 97 7 07 043 1
1997 997 97 7 07 043 1
1997 997 97 7 07 043 1
1997 997 97 7 07 043 1
1997 997 97 7 07 043 1
1997 997 97 7 07 043 1
1997 997 97 7 07 043 1
1997 997 97 7 07 043 1
1997 997 97 7 07 043 1
1997 997 97 7 07 043 1
1997 997 97 7 07 043 1
1997 997 97 7 07 043 1
1997 997 97 7 07 043 1
1997 997 97 7 07 043 1
1997 997 97 7 07 043 1
1997 997 97 7 07 043 1
1997 997 97 7 07 043 1
1997 997 97 7 07 043 1
1997 997 97 7 07 043 1
1997 997 97 7 07 044 2
1997 997 97 7 07 045 3
1997 997 97 7 07 046 4
1997 997 97 7 07 047 5
1997 997 97 7 07 048 6
1997 997 97 7 07 049 7
1997 997 97 7 07 049 7
1997 997 97 7 09 061 5
1997 997 97 7 09 062 6
1997 997 97 7 24 163 2
1997 997 97 7 24 163 2
1998 998 98 8 01 002 2
1998 998 98 8 01 003 3
1999 999 99 9 52 362 5
1999 999 99 9 52 363 6
2000 000 00 0 11 073 3
2000 000 00 0 11 073 3
2000 000 00 0 11 073 3
2000 000 00 0 11 073 3
2000 000 00 0 11 073 3
2000 000 00 0 52 364 7
2001 001 01 1 01 001 1
2001 001 01 1 38 265 6
2097 097 97 7 07 048 6

query T rowsort
SELECT to_char(a, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID') FROM t
----
1097 97 97 7 7 44 2
1697 697 97 7 7 48 6
1797 797 97 7 7 46 4
1897 897 97 7 7 44 2
1996 996 96 6 9 59 3
1996 996 96 6 9 60 4
1996 996 96 6 9 61 5
1997 997 97 7 1 1 1
1997 997 97 7 1 2 2
1997 997 97 7 1 3 3
1997 997 97 7 1 4 4
1997 997 97 7 1 4 4
1997 997 97 7 24 163 2
1997 997 97 7 24 163 2
1997 997 97 7 7 43 1
1997 997 97 7 7 43 1
1997 997 97 7 7 43 1
1997 997 97 7 7 43 1
1997 997 97 7 7 43 1
1997 997 97 7 7 43 1
1997 997 97 7 7 43 1
1997 997 97 7 7 43 1
1997 997 97 7 7 43 1
1997 997 97 7 7 43 1
1997 997 97 7 7 43 1
1997 997 97 7 7 43 1
1997 997 97 7 7 43 1
1997 997 97 7 7 43 1
1997 997 97 7 7 43 1
1997 997 97 7 7 43 1
1997 997 97 7 7 43 1
1997 997 97 7 7 43 1
1997 997 97 7 7 43 1
1997 997 97 7 7 43 1
1997 997 97 7 7 43 1
1997 997 97 7 7 44 2
1997 997 97 7 7 45 3
1997 997 97 7 7 46 4
1997 997 97 7 7 47 5
1997 997 97 7 7 48 6
1997 997 97 7 7 49 7
1997 997 97 7 7 49 7
1997 997 97 7 9 61 5
1997 997 97 7 9 62 6
1998 998 98 8 1 2 2
1998 998 98 8 1 3 3
1999 999 99 9 52 362 5
1999 999 99 9 52 363 6
2000 0 0 0 11 73 3
2000 0 0 0 11 73 3
2000 0 0 0 11 73 3
2000 0 0 0 11 73 3
2000 0 0 0 11 73 3
2000 0 0 0 52 364 7
2001 1 1 1 1 1 1
2001 1 1 1 38 265 6
2097 97 97 7 7 48 6
597 597 97 7 7 46 4
97 97 97 7 7 44 2
97 97 97 7 7 48 6

query T
SELECT to_char(d, 'FF1 FF2 FF3 FF4 FF5 FF6  ff1 ff2 ff3 ff4 ff5 ff6  MS US') FROM (
    VALUES
        ('2018-11-02 12:34:56'::timestamp),
        ('2018-11-02 12:34:56.78'),
        ('2018-11-02 12:34:56.78901'),
        ('2018-11-02 12:34:56.78901234')
    ) d(d)
----
0 00 000 0000 00000 000000  0 00 000 0000 00000 000000  000 000000
7 78 780 7800 78000 780000  7 78 780 7800 78000 780000  780 780000
7 78 789 7890 78901 789010  7 78 789 7890 78901 789010  789 789010
7 78 789 7890 78901 789012  7 78 789 7890 78901 789012  789 789012

# timestamp numeric fields constructor
query T
SELECT make_timestamp(2014, 12, 28, 6, 30, 45.887)
----
2014-12-28 06:30:45.887