aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/test/regress/expected/timestamp.out59
-rw-r--r--src/test/regress/expected/timestamptz.out45
-rw-r--r--src/test/regress/sql/timestamp.sql34
-rw-r--r--src/test/regress/sql/timestamptz.sql27
4 files changed, 126 insertions, 39 deletions
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index 9f585b68f6b..81b3eb87cab 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -1,18 +1,28 @@
--
-- TIMESTAMP
--
-CREATE TABLE TIMESTAMP_TBL ( d1 timestamp(2) without time zone);
--- Shorthand values
--- Not directly usable for regression testing since these are not constants.
--- So, just try to test parser and hope for the best - thomas 97/04/26
--- NB: could get a failure if local midnight passes during the next few
--- statements.
+CREATE TABLE TIMESTAMP_TBL (d1 timestamp(2) without time zone);
+-- Test shorthand input values
+-- We can't just "select" the results since they aren't constants; test for
+-- equality instead. We can do that by running the test inside a transaction
+-- block, within which the value of 'now' shouldn't change. We also check
+-- that 'now' *does* change over a reasonable interval such as 100 msec.
+-- NOTE: it is possible for this part of the test to fail if the transaction
+-- block is entered exactly at local midnight; then 'now' and 'today' have
+-- the same values and the counts will come out different.
+INSERT INTO TIMESTAMP_TBL VALUES ('now');
+SELECT pg_sleep(0.1);
+ pg_sleep
+----------
+
+(1 row)
+
+BEGIN;
INSERT INTO TIMESTAMP_TBL VALUES ('now');
-INSERT INTO TIMESTAMP_TBL VALUES ('current');
-ERROR: date/time value "current" is no longer supported
INSERT INTO TIMESTAMP_TBL VALUES ('today');
INSERT INTO TIMESTAMP_TBL VALUES ('yesterday');
INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow');
+-- time zone should be ignored by this data type
INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow EST');
INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow zulu');
SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'today';
@@ -21,10 +31,10 @@ SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone
1
(1 row)
-SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'tomorrow';
- one
------
- 3
+SELECT count(*) AS Three FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'tomorrow';
+ three
+-------
+ 3
(1 row)
SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'yesterday';
@@ -33,24 +43,37 @@ SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone
1
(1 row)
-SELECT count(*) AS None FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'now';
- none
-------
- 0
+SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp(2) without time zone 'now';
+ one
+-----
+ 1
(1 row)
+COMMIT;
DELETE FROM TIMESTAMP_TBL;
-- verify uniform transaction time within transaction block
BEGIN;
INSERT INTO TIMESTAMP_TBL VALUES ('now');
+SELECT pg_sleep(0.1);
+ pg_sleep
+----------
+
+(1 row)
+
INSERT INTO TIMESTAMP_TBL VALUES ('now');
+SELECT pg_sleep(0.1);
+ pg_sleep
+----------
+
+(1 row)
+
SELECT count(*) AS two FROM TIMESTAMP_TBL WHERE d1 = timestamp(2) without time zone 'now';
two
-----
2
(1 row)
-END;
+COMMIT;
DELETE FROM TIMESTAMP_TBL;
-- Special values
INSERT INTO TIMESTAMP_TBL VALUES ('-infinity');
@@ -59,6 +82,8 @@ INSERT INTO TIMESTAMP_TBL VALUES ('epoch');
-- Obsolete special values
INSERT INTO TIMESTAMP_TBL VALUES ('invalid');
ERROR: date/time value "invalid" is no longer supported
+INSERT INTO TIMESTAMP_TBL VALUES ('current');
+ERROR: date/time value "current" is no longer supported
-- Postgres v6.0 standard output format
INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01 1997 PST');
INSERT INTO TIMESTAMP_TBL VALUES ('Invalid Abstime');
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 706aa099481..92fa5cfaae1 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -1,10 +1,24 @@
--
-- TIMESTAMPTZ
--
-CREATE TABLE TIMESTAMPTZ_TBL ( d1 timestamp(2) with time zone);
+CREATE TABLE TIMESTAMPTZ_TBL (d1 timestamp(2) with time zone);
+-- Test shorthand input values
+-- We can't just "select" the results since they aren't constants; test for
+-- equality instead. We can do that by running the test inside a transaction
+-- block, within which the value of 'now' shouldn't change. We also check
+-- that 'now' *does* change over a reasonable interval such as 100 msec.
+-- NOTE: it is possible for this part of the test to fail if the transaction
+-- block is entered exactly at local midnight; then 'now' and 'today' have
+-- the same values and the counts will come out different.
+INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
+SELECT pg_sleep(0.1);
+ pg_sleep
+----------
+
+(1 row)
+
+BEGIN;
INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
-INSERT INTO TIMESTAMPTZ_TBL VALUES ('current');
-ERROR: date/time value "current" is no longer supported
INSERT INTO TIMESTAMPTZ_TBL VALUES ('today');
INSERT INTO TIMESTAMPTZ_TBL VALUES ('yesterday');
INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow');
@@ -28,24 +42,37 @@ SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone
1
(1 row)
-SELECT count(*) AS None FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'now';
- none
-------
- 0
+SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp(2) with time zone 'now';
+ one
+-----
+ 1
(1 row)
+COMMIT;
DELETE FROM TIMESTAMPTZ_TBL;
-- verify uniform transaction time within transaction block
BEGIN;
INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
+SELECT pg_sleep(0.1);
+ pg_sleep
+----------
+
+(1 row)
+
INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
+SELECT pg_sleep(0.1);
+ pg_sleep
+----------
+
+(1 row)
+
SELECT count(*) AS two FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp(2) with time zone 'now';
two
-----
2
(1 row)
-END;
+COMMIT;
DELETE FROM TIMESTAMPTZ_TBL;
-- Special values
INSERT INTO TIMESTAMPTZ_TBL VALUES ('-infinity');
@@ -54,6 +81,8 @@ INSERT INTO TIMESTAMPTZ_TBL VALUES ('epoch');
-- Obsolete special values
INSERT INTO TIMESTAMPTZ_TBL VALUES ('invalid');
ERROR: date/time value "invalid" is no longer supported
+INSERT INTO TIMESTAMPTZ_TBL VALUES ('current');
+ERROR: date/time value "current" is no longer supported
-- Postgres v6.0 standard output format
INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01 1997 PST');
INSERT INTO TIMESTAMPTZ_TBL VALUES ('Invalid Abstime');
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index 81be5b8f33f..bfbfb87feb8 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -2,35 +2,48 @@
-- TIMESTAMP
--
-CREATE TABLE TIMESTAMP_TBL ( d1 timestamp(2) without time zone);
+CREATE TABLE TIMESTAMP_TBL (d1 timestamp(2) without time zone);
--- Shorthand values
--- Not directly usable for regression testing since these are not constants.
--- So, just try to test parser and hope for the best - thomas 97/04/26
--- NB: could get a failure if local midnight passes during the next few
--- statements.
+-- Test shorthand input values
+-- We can't just "select" the results since they aren't constants; test for
+-- equality instead. We can do that by running the test inside a transaction
+-- block, within which the value of 'now' shouldn't change. We also check
+-- that 'now' *does* change over a reasonable interval such as 100 msec.
+-- NOTE: it is possible for this part of the test to fail if the transaction
+-- block is entered exactly at local midnight; then 'now' and 'today' have
+-- the same values and the counts will come out different.
+
+INSERT INTO TIMESTAMP_TBL VALUES ('now');
+SELECT pg_sleep(0.1);
+
+BEGIN;
INSERT INTO TIMESTAMP_TBL VALUES ('now');
-INSERT INTO TIMESTAMP_TBL VALUES ('current');
INSERT INTO TIMESTAMP_TBL VALUES ('today');
INSERT INTO TIMESTAMP_TBL VALUES ('yesterday');
INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow');
+-- time zone should be ignored by this data type
INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow EST');
INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow zulu');
SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'today';
-SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'tomorrow';
+SELECT count(*) AS Three FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'tomorrow';
SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'yesterday';
-SELECT count(*) AS None FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'now';
+SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp(2) without time zone 'now';
+
+COMMIT;
DELETE FROM TIMESTAMP_TBL;
-- verify uniform transaction time within transaction block
BEGIN;
INSERT INTO TIMESTAMP_TBL VALUES ('now');
+SELECT pg_sleep(0.1);
INSERT INTO TIMESTAMP_TBL VALUES ('now');
+SELECT pg_sleep(0.1);
SELECT count(*) AS two FROM TIMESTAMP_TBL WHERE d1 = timestamp(2) without time zone 'now';
-END;
+COMMIT;
+
DELETE FROM TIMESTAMP_TBL;
-- Special values
@@ -39,6 +52,7 @@ INSERT INTO TIMESTAMP_TBL VALUES ('infinity');
INSERT INTO TIMESTAMP_TBL VALUES ('epoch');
-- Obsolete special values
INSERT INTO TIMESTAMP_TBL VALUES ('invalid');
+INSERT INTO TIMESTAMP_TBL VALUES ('current');
-- Postgres v6.0 standard output format
INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01 1997 PST');
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index 731855d584d..07d8bd357ba 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -2,10 +2,23 @@
-- TIMESTAMPTZ
--
-CREATE TABLE TIMESTAMPTZ_TBL ( d1 timestamp(2) with time zone);
+CREATE TABLE TIMESTAMPTZ_TBL (d1 timestamp(2) with time zone);
+
+-- Test shorthand input values
+-- We can't just "select" the results since they aren't constants; test for
+-- equality instead. We can do that by running the test inside a transaction
+-- block, within which the value of 'now' shouldn't change. We also check
+-- that 'now' *does* change over a reasonable interval such as 100 msec.
+-- NOTE: it is possible for this part of the test to fail if the transaction
+-- block is entered exactly at local midnight; then 'now' and 'today' have
+-- the same values and the counts will come out different.
+
+INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
+SELECT pg_sleep(0.1);
+
+BEGIN;
INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
-INSERT INTO TIMESTAMPTZ_TBL VALUES ('current');
INSERT INTO TIMESTAMPTZ_TBL VALUES ('today');
INSERT INTO TIMESTAMPTZ_TBL VALUES ('yesterday');
INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow');
@@ -15,16 +28,21 @@ INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow zulu');
SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'today';
SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow';
SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'yesterday';
-SELECT count(*) AS None FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'now';
+SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp(2) with time zone 'now';
+
+COMMIT;
DELETE FROM TIMESTAMPTZ_TBL;
-- verify uniform transaction time within transaction block
BEGIN;
INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
+SELECT pg_sleep(0.1);
INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
+SELECT pg_sleep(0.1);
SELECT count(*) AS two FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp(2) with time zone 'now';
-END;
+COMMIT;
+
DELETE FROM TIMESTAMPTZ_TBL;
-- Special values
@@ -33,6 +51,7 @@ INSERT INTO TIMESTAMPTZ_TBL VALUES ('infinity');
INSERT INTO TIMESTAMPTZ_TBL VALUES ('epoch');
-- Obsolete special values
INSERT INTO TIMESTAMPTZ_TBL VALUES ('invalid');
+INSERT INTO TIMESTAMPTZ_TBL VALUES ('current');
-- Postgres v6.0 standard output format
INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01 1997 PST');
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-02 11:05:24 +0000