[
https://issues.apache.org/jira/browse/FLINK-25304?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17459121#comment-17459121
]
Marios Trivyzas commented on FLINK-25304:
-----------------------------------------
Oracle pads with trailing 0 to match the precision
{noformat}
select CAST(CAST(TO_TIMESTAMP('2021-02-05 11:22:33.123456', 'YYYY-MM-DD
HH24:MI:SS.FF6') AS TIMESTAMP(9)) AS VARCHAR(100)) FROM DUAL;
=>
05-FEB-21 11.22.33.123456000 AM
select CAST(CAST(TO_TIMESTAMP('2021-02-05 11:22:33.123456', 'YYYY-MM-DD
HH24:MI:SS.FF6') AS TIMESTAMP(7)) AS VARCHAR(100)) FROM DUAL;
=>
05-FEB-21 11.22.33.1234560 AM
select CAST(CAST(TO_TIMESTAMP('2021-02-05 11:22:33.123456', 'YYYY-MM-DD
HH24:MI:SS.FF6') AS TIMESTAMP(6)) AS VARCHAR(100)) FROM DUAL;
=>
05-FEB-21 11.22.33.123456 AM
select CAST(CAST(TO_TIMESTAMP('2021-02-05 11:22:33', 'YYYY-MM-DD HH24:MI:SS')
AS TIMESTAMP(2)) AS VARCHAR(100)) FROM DUAL;
=>
05-FEB-21 11.22.33.00 AM
select CAST(CAST(TO_TIMESTAMP('2021-02-05 11:22:33', 'YYYY-MM-DD HH24:MI:SS')
AS TIMESTAMP(0)) AS VARCHAR(100)) FROM DUAL;
=>
05-FEB-21 11.22.33 AM{noformat}
MySQL does also apply padding:
{noformat}
select CAST(CAST(TIMESTAMP('2021-12-13 11:22:33.1234') AS CHAR) AS DATETIME(6))
FROM DUAL;
=>
2021-12-13 11:22:33.123400{noformat}
Postgres does no padding, even not for the first 3 fractional digits
(milliseconds)
{noformat}
postgres=# select '2021-12-13 11:22:33.1234'::timestamp(6)::varchar(50);
varchar
--------------------------
2021-12-13 11:22:33.1234
(1 row)
postgres=# select '2021-12-13 11:22:33.123400'::timestamp(6)::varchar(50);
varchar
--------------------------
2021-12-13 11:22:33.1234
(1 row)
postgres=# select '2021-12-13 11:22:33.12'::timestamp(6)::varchar(50);
varchar
------------------------
2021-12-13 11:22:33.12
(1 row)
postgres=# select '2021-12-13 11:22:33.1'::timestamp(6)::varchar(50);
varchar
-----------------------
2021-12-13 11:22:33.1
(1 row)
postgres=# select '2021-12-13 11:22:33'::timestamp(6)::varchar(50);
varchar
---------------------
2021-12-13 11:22:33
(1 row){noformat}
> TIMESTAMP/TIMESTAMP_LTZ Pad with trailing zeros for fractional seconds
> depending on the precision specified
> -----------------------------------------------------------------------------------------------------------
>
> Key: FLINK-25304
> URL: https://issues.apache.org/jira/browse/FLINK-25304
> Project: Flink
> Issue Type: Sub-task
> Reporter: Marios Trivyzas
> Priority: Major
>
> Should we pad with trailing zeros for the fractional second part?
> e.g.:
>
> {noformat}
> CAST(CAST('2021-12-13 11:22:33.123456' AS TIMESTAMP(9) AS STRING){noformat}
> Should it return:
>
> {noformat}
> 2021-12-13 11:22:33.123456{noformat}
> or:
> {noformat}
> 2021-12-13 11:22:33.123456000{noformat}
> ?
>
>
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
