Bug: TDVT StandardTests datetime.cast.str fails (different time format) #17
Description
TRIM(STR([datetime0])) fails due to different time format
| SQL | Actual | Expected |
|---|---|---|
| SELECT BTRIM(CAST(CAST("calcs"."datetime0" AS TEXT) AS TEXT), ' ') AS "TEMP(Test)(1103404331)(0)" FROM "calcs" GROUP BY BTRIM(CAST(CAST("calcs"."datetime0" AS TEXT) AS TEXT), ' ') | "2004-07-04T22:49:28", "2004-07-05T13:14:20", "2004-07-09T10:17:35", "2004-07-12T17:30:16", "2004-07-14T07:43:00", "2004-07-14T08:16:44", "2004-07-17T14:01:56", "2004-07-19T22:21:31", "2004-07-22T00:30:23", "2004-07-23T21:13:37", "2004-07-25T15:22:26", "2004-07-26T12:30:34", "2004-07-28T06:54:50", "2004-07-28T12:34:28", "2004-07-28T23:30:22", "2004-07-31T11:57:52", "2004-08-02T07:59:23" | "Jul 4 2004 10:49PM", "Jul 5 2004 1:14PM", "Jul 9 2004 10:17AM", "Jul 12 2004 5:30PM", "Jul 14 2004 7:43AM", "Jul 14 2004 8:16AM", "Jul 17 2004 2:01PM", "Jul 19 2004 10:21PM", "Jul 22 2004 12:30AM", "Jul 23 2004 9:13PM", "Jul 25 2004 3:22PM", "Jul 26 2004 12:30PM", "Jul 28 2004 6:54AM", "Jul 28 2004 12:34PM", "Jul 28 2004 11:30PM", "Jul 31 2004 11:57AM", "Aug 2 2004 7:59AM" |
It seems to be specific to handling datetime configuration as if I run the same query I get similar format as Spice uses
WITH calcs AS (
SELECT TIMESTAMP '2025-04-10 08:00:00' AS datetime0
UNION ALL
SELECT TIMESTAMP '2025-04-10 08:00:00'
UNION ALL
SELECT TIMESTAMP '2025-04-10 09:30:00'
UNION ALL
SELECT TIMESTAMP '2025-04-10 08:00:00+00'
)
SELECT
BTRIM(CAST(CAST("calcs"."datetime0" AS TEXT) AS TEXT), ' ') AS "TEMP(Test)(1103404331)(0)"
FROM calcs
GROUP BY
BTRIM(CAST(CAST("calcs"."datetime0" AS TEXT) AS TEXT), ' ');
2025-04-10 09:30:00
2025-04-10 08:00:00