[PHOENIX-1748] Applying TRUNC|ROUND|FLOOR|CEIL on TIMESTAMP should maintain return type of TIMESTAMP

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.3.0
• Labels: Newbie
• Assignee: Unassigned
• Reporter: Serhiy Bilousov
• Created: 18/Mar/15 23:24
• Updated: 24/Jan/16 01:41
• Resolved: 24/Jan/16 01:41

Description

Given that input value is "YYYY-MM-DD HH:MM:SS.nnn" type of TIMESTAMP (UNSIGNED_TIMESTAMP, DATE, TIME etc)
When applying TRUNC|ROUND|FLOOR|CEIL with DAY|HOUR|MINUTE|SECOND|MILLISECOND
Than result should be "YYYY-MM-DD HH:MM:SS.nnn"
But "YYYY-MM-DD" is returned instead.

Basically when I do TRUNC on timestamp I would expect it to be timestamp with relevant parts truncated so for example I can GROUP BY on TRUNC (timestamp,'HOUR') and have my hourly aggregation.

Here is test queries with cast(current_date() AS timestamp).

 SELECT
    dt
    ,TRUNC(dt,'DAY') AS trunc_day_from_dt
    ,TRUNC(dt,'HOUR') AS trunc_hour_from_dt
    ,TRUNC(dt,'MINUTE') AS trunc_min_from_dt
    ,TRUNC(dt,'SECOND') AS trunc_sec_from_dt
    ,TRUNC(dt,'MILLISECOND') AS trunc_mil_from_dt
 FROM
    (SELECT current_date() AS d, cast(current_date() AS timestamp) AS dt, TO_NUMBER(current_date()) e FROM system.catalog LIMIT 1) t;
+------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| TO_TIMESTAMP('2015-03-08 09:09:11.665')  |  TRUNC_DAY_FROM_DT  | TRUNC_HOUR_FROM_DT  |  TRUNC_MIN_FROM_DT  |  TRUNC_SEC_FROM_DT  |  TRUNC_MIL_FROM_DT  |
+------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2015-03-08 09:09:11.665                  | 2015-03-08          | 2015-03-08          | 2015-03-08          | 2015-03-08          | 2015-03-08          |
+------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
1 row selected (0.066 seconds)

 SELECT
    dt
    ,ROUND(dt,'DAY') AS round_day_from_d
    ,ROUND(dt,'HOUR') AS round_hour_from_d
    ,ROUND(dt,'MINUTE') AS round_min_from_d
    ,ROUND(dt,'SECOND') AS round_sec_from_d
    ,ROUND(dt,'MILLISECOND') AS round_mil_from_d
 FROM
    (SELECT current_date() AS d, cast(current_date() AS timestamp) AS dt, TO_NUMBER(current_date()) e FROM system.catalog LIMIT 1) t;
+------------------------------------------+---------------------+---------------------+---------------------+---------------------+------------------------------------------+
| TO_TIMESTAMP('2015-03-08 09:09:11.782')  |  ROUND_DAY_FROM_D   |  ROUND_HOUR_FROM_D  |  ROUND_MIN_FROM_D   |  ROUND_SEC_FROM_D   |             ROUND_MIL_FROM_D             |
+------------------------------------------+---------------------+---------------------+---------------------+---------------------+------------------------------------------+
| 2015-03-08 09:09:11.782                  | 2015-03-08          | 2015-03-08          | 2015-03-08          | 2015-03-08          | 2015-03-08 09:09:11.782                  |
+------------------------------------------+---------------------+---------------------+---------------------+---------------------+------------------------------------------+
1 row selected (0.06 seconds)

 SELECT
    dt
    ,FLOOR(dt,'DAY') AS floor_day_from_dt
    ,FLOOR(dt,'HOUR') AS floor_hour_from_dt
    ,FLOOR(dt,'MINUTE') AS floor_min_from_dt
    ,FLOOR(dt,'SECOND') AS floor_sec_from_dt
    ,FLOOR(dt,'MILLISECOND') AS floor_mil_from_dt
 FROM
    (SELECT current_date() AS d, cast(current_date() AS timestamp) AS dt, TO_NUMBER(current_date()) e FROM system.catalog LIMIT 1) t;
+------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| TO_TIMESTAMP('2015-03-08 09:09:11.895')  |  FLOOR_DAY_FROM_DT  | FLOOR_HOUR_FROM_DT  |  FLOOR_MIN_FROM_DT  |  FLOOR_SEC_FROM_DT  |  FLOOR_MIL_FROM_DT  |
+------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2015-03-08 09:09:11.895                  | 2015-03-08          | 2015-03-08          | 2015-03-08          | 2015-03-08          | 2015-03-08          |
+------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
1 row selected (0.059 seconds)

 SELECT
    dt
    ,CEIL(dt,'DAY') AS ceil_day_from_dt
    ,CEIL(dt,'HOUR') AS ceil_hour_from_dt
    ,CEIL(dt,'MINUTE') AS ceil_min_from_dt
    ,CEIL(dt,'SECOND') AS ceil_sec_from_dt
    ,CEIL(dt,'MILLISECOND') AS ceil_mil_from_dt
 FROM
    (SELECT current_date() AS d, cast(current_date() AS timestamp) AS dt, TO_NUMBER(current_date()) e FROM system.catalog LIMIT 1) t;
+------------------------------------------+---------------------+---------------------+---------------------+---------------------+------------------------------------------+
| TO_TIMESTAMP('2015-03-08 09:09:12.009')  |  CEIL_DAY_FROM_DT   |  CEIL_HOUR_FROM_DT  |  CEIL_MIN_FROM_DT   |  CEIL_SEC_FROM_DT   |             CEIL_MIL_FROM_DT             |
+------------------------------------------+---------------------+---------------------+---------------------+---------------------+------------------------------------------+
| 2015-03-08 09:09:12.009                  | 2015-03-09          | 2015-03-08          | 2015-03-08          | 2015-03-08          | 2015-03-08 09:09:12.009                  |
+------------------------------------------+---------------------+---------------------+---------------------+---------------------+------------------------------------------+
1 row selected (0.061 seconds)