Inconsistent behavior for to_unixtime(timestamp(p))

[martint]

Trino currently has two variants of to_unixtime:

• to_unixtime(timestamp(p))
• to_unixtime(timestamp(p) with time zone)

After the timestamp semantics fix (#37), the behavior of the first variant became inconsistent with the second variant. Unix times represent a point in time (vs a wall clock time), so, conceptually, a timestamp(p) first needs to be converted to a timestamp(p) with time zone before the transformation is applied.

Currently, such timestamps are interpreted as being UTC, which is confusing and counter-intuitive, especially in comparison with an invocation that uses an explicit cast:

SELECT 
    to_unixtime(localtimestamp), 
    to_unixtime(cast(localtimestamp as timestamp with time zone))

=>

      _col0       |      _col1
------------------+------------------
 1.617009524129E9 | 1.617034724129E9

In a way, the function should only be defined for timestamp(p) with time zone and regular coercion rules for timestamp(p) -> timestamp(p) with time zone should apply

This can be fixed simply by removing io.trino.operator.scalar.timestamp.ToUnixTime

[GuoPhilipse]

hi @martint , if so, maybe we can redefine function from_unixtime, it will cause inconsistent as well.

trino:default> select from_unixtime(1323308943,'Asia/Shanghai');
                 _col0                 
---------------------------------------
 2011-12-08 09:49:03.000 Asia/Shanghai 
(1 row)

Query 20210330_081203_00068_b8csa, FINISHED, 1 node
Splits: 1 total, 1 done (100.00%)
0.26 [0 rows, 0B] [0 rows/s, 0B/s]

trino:default> select from_unixtime(1323308943);
          _col0          
-------------------------
 2011-12-08 01:49:03.000 
(1 row)

Query 20210330_081210_00069_b8csa, FINISHED, 1 node
Splits: 1 total, 1 done (100.00%)
0.24 [0 rows, 0B] [0 rows/s, 0B/s]

[GuoPhilipse]

hi ,@martint will you work on it ? if you don't have time , i am glad to take on it:)

[martint]

@GuoPhilipse, I posted a PR for this issue.