[jira] [Commented] (PHOENIX-6807) Change return type of PHOENIX_ROW_TIMESTAMP() function from DATE -> TIMESTAMP

["ASF GitHub Bot (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/PHOENIX-6807?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17614235#comment-17614235 ] 

ASF GitHub Bot commented on PHOENIX-6807:
-----------------------------------------

tkhurana opened a new pull request, #1516:
URL: https://github.com/apache/phoenix/pull/1516

   Return timestamp instead of date.




> Change return type of PHOENIX_ROW_TIMESTAMP() function from DATE -> TIMESTAMP
> -----------------------------------------------------------------------------
>
>                 Key: PHOENIX-6807
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-6807
>             Project: Phoenix
>          Issue Type: Improvement
>    Affects Versions: 5.2.0, 5.1.2
>            Reporter: Tanuj Khurana
>            Assignee: Tanuj Khurana
>            Priority: Minor
>
> Today, PHOENIX_ROW_TIMESTAMP() function returns a DATE data type. This causes multiple issues:
> {code:java}
> // 0: jdbc:phoenix:localhost> create table T (id varchar primary key, ts timestamp);
> No rows affected (0.703 seconds)
> 0: jdbc:phoenix:localhost> upsert into T values('a', TO_TIMESTAMP('2005-10-01 14:03:22.559'));
> 1 row affected (0.05 seconds)
> 0: jdbc:phoenix:localhost> upsert into T values('b', TO_TIMESTAMP('2015-09-01 23:03:22.559'));
> 1 row affected (0.005 seconds)
> 0: jdbc:phoenix:localhost> upsert into T values('c', TO_TIMESTAMP('2022-09-01 03:03:24.897'));
> 1 row affected (0.008 seconds)
> 0: jdbc:phoenix:localhost> !outputformat csv
> 0: jdbc:phoenix:localhost> select ts, PHOENIX_ROW_TIMESTAMP() from T;
> 'TS','PHOENIX_ROW_TIMESTAMP(0.)'
> '2005-10-01 07:03:22.559','2022-10-07'
> '2015-09-01 16:03:22.559','2022-10-07'
> '2022-08-31 20:03:24.897','2022-10-07'
> 3 rows selected (0.012 seconds) {code}
> Notice, how the time component is dropped when we use sqlline to print PHOENIX_ROW_TIMESTAMP() values.  In comparison, the timestamp column is displayed correctly. This is a major drawback IMO since one of the primary motivation of implementing the PHOENIX_ROW_TIMESTAMP() function was to aid in debugging.
> There is another issue with returning DATE type. Consider the query below:
> {code:java}
> SELECT * from T where PHOENIX_ROW_TIMESTAMP() = <Timestamp value>{code}
> This query always returns 0 rows. This is because the timestamp value which is 12 bytes can't be coerced to a DATE type so the where compiler compiles the equality expression to an always *FALSE* expression.
> I propose changing the return type of PHOENIX_ROW_TIMESTAMP() to TIMESTAMP. It solves both the issues listed above and makes sense since PHOENIX_ROW_TIMESTAMP has timestamp in it :)



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