🌐 AI搜索 & 代理 主页
Skip to content

Commit a9a7c2c

Browse files
tglsfdctglsfdc
committed
Fix input of ISO "extended" time format for types time and timetz.
Commit 3e1a373 missed teaching DecodeTimeOnly the same "ptype" manipulations it added to DecodeDateTime. While likely harmless at the time, it became a problem after 5b3c595 added an error check that ptype must be zero once we exit the parsing loop (that is, there shouldn't be any unused prefixes). The consequence was that we'd reject time or timetz input like T12:34:56 (the "extended" format per ISO 8601-1:2019), even though that still worked in timestamp input. Since this is clearly under-tested code, add test cases covering all the ISO 8601 time formats. (Note: although 8601 allows just "Thh", we have never accepted that, and this patch doesn't change that. I'm content to leave that as-is because it seems too likely to be a mistake rather than intended input. If anyone wants to allow that, it should be a separate patch anyway, and not back-patched.) Per bug #18470 from David Perez. Back-patch to v16 where we broke it. Discussion: https://postgr.es/m/18470-34fad4c829106848@postgresql.org
1 parent d2a338e commit a9a7c2c

File tree

3 files changed

+195
-0
lines changed

3 files changed

+195
-0
lines changed

‎src/backend/utils/adt/datetime.c‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1970,6 +1970,17 @@ DecodeTimeOnly(char **field, int *ftype, int nf,
19701970
break;
19711971

19721972
case DTK_TIME:
1973+
1974+
/*
1975+
* This might be an ISO time following a "t" field.
1976+
*/
1977+
if (ptype != 0)
1978+
{
1979+
if (ptype != DTK_TIME)
1980+
return DTERR_BAD_FORMAT;
1981+
ptype = 0;
1982+
}
1983+
19731984
dterr = DecodeTime(field[i], (fmask | DTK_DATE_M),
19741985
INTERVAL_FULL_RANGE,
19751986
&tmask, tm, fsec);

‎src/test/regress/expected/horology.out‎

Lines changed: 155 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -274,6 +274,161 @@ SELECT time with time zone 'J2452271 T040506.789 America/Los_Angeles';
274274
04:05:06.789-08
275275
(1 row)
276276

277+
-- Check time formats required by ISO 8601
278+
SELECT time without time zone '040506.07';
279+
time
280+
-------------
281+
04:05:06.07
282+
(1 row)
283+
284+
SELECT time without time zone '04:05:06.07';
285+
time
286+
-------------
287+
04:05:06.07
288+
(1 row)
289+
290+
SELECT time without time zone '040506';
291+
time
292+
----------
293+
04:05:06
294+
(1 row)
295+
296+
SELECT time without time zone '04:05:06';
297+
time
298+
----------
299+
04:05:06
300+
(1 row)
301+
302+
SELECT time without time zone '0405';
303+
time
304+
----------
305+
04:05:00
306+
(1 row)
307+
308+
SELECT time without time zone '04:05';
309+
time
310+
----------
311+
04:05:00
312+
(1 row)
313+
314+
SELECT time without time zone 'T040506.07';
315+
time
316+
-------------
317+
04:05:06.07
318+
(1 row)
319+
320+
SELECT time without time zone 'T04:05:06.07';
321+
time
322+
-------------
323+
04:05:06.07
324+
(1 row)
325+
326+
SELECT time without time zone 'T040506';
327+
time
328+
----------
329+
04:05:06
330+
(1 row)
331+
332+
SELECT time without time zone 'T04:05:06';
333+
time
334+
----------
335+
04:05:06
336+
(1 row)
337+
338+
SELECT time without time zone 'T0405';
339+
time
340+
----------
341+
04:05:00
342+
(1 row)
343+
344+
SELECT time without time zone 'T04:05';
345+
time
346+
----------
347+
04:05:00
348+
(1 row)
349+
350+
-- 8601 says "Thh" is allowed, but we intentionally reject it as too vague
351+
SELECT time without time zone 'T04';
352+
ERROR: invalid input syntax for type time: "T04"
353+
LINE 1: SELECT time without time zone 'T04';
354+
^
355+
SELECT time with time zone '040506.07+08';
356+
timetz
357+
----------------
358+
04:05:06.07+08
359+
(1 row)
360+
361+
SELECT time with time zone '04:05:06.07+08';
362+
timetz
363+
----------------
364+
04:05:06.07+08
365+
(1 row)
366+
367+
SELECT time with time zone '040506+08';
368+
timetz
369+
-------------
370+
04:05:06+08
371+
(1 row)
372+
373+
SELECT time with time zone '04:05:06+08';
374+
timetz
375+
-------------
376+
04:05:06+08
377+
(1 row)
378+
379+
SELECT time with time zone '0405+08';
380+
timetz
381+
-------------
382+
04:05:00+08
383+
(1 row)
384+
385+
SELECT time with time zone '04:05+08';
386+
timetz
387+
-------------
388+
04:05:00+08
389+
(1 row)
390+
391+
SELECT time with time zone 'T040506.07+08';
392+
timetz
393+
----------------
394+
04:05:06.07+08
395+
(1 row)
396+
397+
SELECT time with time zone 'T04:05:06.07+08';
398+
timetz
399+
----------------
400+
04:05:06.07+08
401+
(1 row)
402+
403+
SELECT time with time zone 'T040506+08';
404+
timetz
405+
-------------
406+
04:05:06+08
407+
(1 row)
408+
409+
SELECT time with time zone 'T04:05:06+08';
410+
timetz
411+
-------------
412+
04:05:06+08
413+
(1 row)
414+
415+
SELECT time with time zone 'T0405+08';
416+
timetz
417+
-------------
418+
04:05:00+08
419+
(1 row)
420+
421+
SELECT time with time zone 'T04:05+08';
422+
timetz
423+
-------------
424+
04:05:00+08
425+
(1 row)
426+
427+
-- 8601 says "Thh" is allowed, but we intentionally reject it as too vague
428+
SELECT time with time zone 'T04+08';
429+
ERROR: invalid input syntax for type time with time zone: "T04+08"
430+
LINE 1: SELECT time with time zone 'T04+08';
431+
^
277432
SET DateStyle = 'Postgres, MDY';
278433
-- Check Julian dates BC
279434
SELECT date 'J1520447' AS "Confucius' Birthday";

‎src/test/regress/sql/horology.sql‎

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -59,6 +59,35 @@ SELECT time with time zone 'T040506.789 -08';
5959
SELECT time with time zone 'T040506.789 America/Los_Angeles';
6060
SELECT time with time zone '2001-12-27 T040506.789 America/Los_Angeles';
6161
SELECT time with time zone 'J2452271 T040506.789 America/Los_Angeles';
62+
-- Check time formats required by ISO 8601
63+
SELECT time without time zone '040506.07';
64+
SELECT time without time zone '04:05:06.07';
65+
SELECT time without time zone '040506';
66+
SELECT time without time zone '04:05:06';
67+
SELECT time without time zone '0405';
68+
SELECT time without time zone '04:05';
69+
SELECT time without time zone 'T040506.07';
70+
SELECT time without time zone 'T04:05:06.07';
71+
SELECT time without time zone 'T040506';
72+
SELECT time without time zone 'T04:05:06';
73+
SELECT time without time zone 'T0405';
74+
SELECT time without time zone 'T04:05';
75+
-- 8601 says "Thh" is allowed, but we intentionally reject it as too vague
76+
SELECT time without time zone 'T04';
77+
SELECT time with time zone '040506.07+08';
78+
SELECT time with time zone '04:05:06.07+08';
79+
SELECT time with time zone '040506+08';
80+
SELECT time with time zone '04:05:06+08';
81+
SELECT time with time zone '0405+08';
82+
SELECT time with time zone '04:05+08';
83+
SELECT time with time zone 'T040506.07+08';
84+
SELECT time with time zone 'T04:05:06.07+08';
85+
SELECT time with time zone 'T040506+08';
86+
SELECT time with time zone 'T04:05:06+08';
87+
SELECT time with time zone 'T0405+08';
88+
SELECT time with time zone 'T04:05+08';
89+
-- 8601 says "Thh" is allowed, but we intentionally reject it as too vague
90+
SELECT time with time zone 'T04+08';
6291
SET DateStyle = 'Postgres, MDY';
6392
-- Check Julian dates BC
6493
SELECT date 'J1520447' AS "Confucius' Birthday";

0 commit comments

Comments
 (0)