You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@ignite.apache.org by "Maksim Zhuravkov (Jira)" <ji...@apache.org> on 2025/05/06 15:11:00 UTC
[jira] [Updated] (IGNITE-25313) Sql. Time, Timestamp, Timestamp LTZ. Precision. CAST FORMAT confusing behaviour when translating milliseconds

     [ https://issues.apache.org/jira/browse/IGNITE-25313?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Maksim Zhuravkov updated IGNITE-25313:
--------------------------------------
    Description: 
1. 100ms -> 1 ms caused by calcite's parser uses SimpleDateFormat that parses milliseconds this way.
2. parse operation completely loses milliseconds

{noformat}
// TIME
SELECT CAST('12:00:00.1' AS TIME(3) FORMAT 'hh24:mi:ss.ff3');
12:00:00.001  

// milliseconds are lost
SELECT CAST('12:00:00.1' AS TIME(2) FORMAT 'hh24:mi:ss.ff2');
12:00  

// milliseconds are lost
SELECT CAST('12:00:00.1' AS TIME(1) FORMAT 'hh24:mi:ss.ff1');
12:00  

// TIMESTAMP

// 100 ms -> 1 ms 
SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(3) FORMAT 'yyyy-MM-dd hh24:mi:ss.ff3');
2020-05-01T12:00:00.001 

// milliseconds are lost
SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(2) FORMAT 'yyyy-MM-dd hh24:mi:ss.ff2');
2020-05-01T12:00  

// milliseconds are lost
SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(1) FORMAT 'yyyy-MM-dd hh24:mi:ss.ff1');
 2020-05-01T12:00  

// TIMESTAMP LTZ

// 100 ms -> 1 ms (calcite's formatter uses SimpleDateFormat)
SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(3) WITH LOCAL TIME ZONE FORMAT 'yyyy-MM-dd hh24:mi:ss.ff3');
2020-05-01T09:00:00.001Z  

SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(2) WITH LOCAL TIME ZONE FORMAT 'yyyy-MM-dd hh24:mi:ss.ff2');
2020-05-01T09:00:00.001Z     

SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(1) WITH LOCAL TIME ZONE FORMAT 'yyyy-MM-dd hh24:mi:ss.ff1');
2020-05-01T09:00:00.001Z  
{noformat}



  was:
{noformat}
// TIME
SELECT CAST('12:00:00.1' AS TIME(3) FORMAT 'hh24:mi:ss.ff3');
12:00:00.001  

SELECT CAST('12:00:00.1' AS TIME(2) FORMAT 'hh24:mi:ss.ff2');
12:00  

SELECT CAST('12:00:00.1' AS TIME(1) FORMAT 'hh24:mi:ss.ff1');
12:00  

// TIMESTAMP
SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(3) FORMAT 'yyyy-MM-dd hh24:mi:ss.ff3');
2020-05-01T12:00:00.001 

SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(2) FORMAT 'yyyy-MM-dd hh24:mi:ss.ff2');
 2020-05-01T12:00  

SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(1) FORMAT 'yyyy-MM-dd hh24:mi:ss.ff1');
 2020-05-01T12:00  

// TIMESTAMP LTZ

 SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(3) WITH LOCAL TIME ZONE FORMAT 'yyyy-MM-dd hh24:mi:ss.ff3');
 2020-05-01T09:00:00.001Z  

 SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(2) WITH LOCAL TIME ZONE FORMAT 'yyyy-MM-dd hh24:mi:ss.ff2');
 2020-05-01T09:00:00.001Z     

 SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(1) WITH LOCAL TIME ZONE FORMAT 'yyyy-MM-dd hh24:mi:ss.ff1');
 2020-05-01T09:00:00.001Z  
{noformat}




> Sql. Time, Timestamp, Timestamp LTZ. Precision. CAST FORMAT confusing behaviour when translating milliseconds
> -------------------------------------------------------------------------------------------------------------
>
>                 Key: IGNITE-25313
>                 URL: https://issues.apache.org/jira/browse/IGNITE-25313
>             Project: Ignite
>          Issue Type: Bug
>          Components: sql
>            Reporter: Maksim Zhuravkov
>            Priority: Major
>              Labels: ignite-3
>
> 1. 100ms -> 1 ms caused by calcite's parser uses SimpleDateFormat that parses milliseconds this way.
> 2. parse operation completely loses milliseconds
> {noformat}
> // TIME
> SELECT CAST('12:00:00.1' AS TIME(3) FORMAT 'hh24:mi:ss.ff3');
> 12:00:00.001  
> // milliseconds are lost
> SELECT CAST('12:00:00.1' AS TIME(2) FORMAT 'hh24:mi:ss.ff2');
> 12:00  
> // milliseconds are lost
> SELECT CAST('12:00:00.1' AS TIME(1) FORMAT 'hh24:mi:ss.ff1');
> 12:00  
> // TIMESTAMP
> // 100 ms -> 1 ms 
> SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(3) FORMAT 'yyyy-MM-dd hh24:mi:ss.ff3');
> 2020-05-01T12:00:00.001 
> // milliseconds are lost
> SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(2) FORMAT 'yyyy-MM-dd hh24:mi:ss.ff2');
> 2020-05-01T12:00  
> // milliseconds are lost
> SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(1) FORMAT 'yyyy-MM-dd hh24:mi:ss.ff1');
>  2020-05-01T12:00  
> // TIMESTAMP LTZ
> // 100 ms -> 1 ms (calcite's formatter uses SimpleDateFormat)
> SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(3) WITH LOCAL TIME ZONE FORMAT 'yyyy-MM-dd hh24:mi:ss.ff3');
> 2020-05-01T09:00:00.001Z  
> SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(2) WITH LOCAL TIME ZONE FORMAT 'yyyy-MM-dd hh24:mi:ss.ff2');
> 2020-05-01T09:00:00.001Z     
> SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(1) WITH LOCAL TIME ZONE FORMAT 'yyyy-MM-dd hh24:mi:ss.ff1');
> 2020-05-01T09:00:00.001Z  
> {noformat}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)