About Databend timestamp

[TCeason]

In snowflake, the default timestamp is timestamp_ntz which means timestamp without time zone.

https://docs.snowflake.com/en/sql-reference/data-types-datetime.html#timestamp-ltz-timestamp-ntz-timestamp-tz

TIMESTAMP_NTZ
TIMESTAMP_NTZ internally stores “wallclock” time with a specified precision. All operations are performed without taking any time zone into account.

If the output format contains a time zone, the UTC indicator (Z) is displayed.

TIMESTAMP_NTZ is the default for TIMESTAMP.

Aliases for TIMESTAMP_NTZ:

TIMESTAMPNTZ

TIMESTAMP WITHOUT TIME ZONE

And I see some diff

create or replace table ts_test(ts timestamp_ntz);

alter session set timezone = 'America/Los_Angeles';

insert into ts_test values('2014-01-01 16:00:00');
insert into ts_test values('2014-01-02 16:00:00 +00:00');

-- Note that both times from different time zones are converted to the same "wallclock" time

select ts, hour(ts) from ts_test;

+---------------------------+----------+
| TS                        | HOUR(TS) |
|---------------------------+----------|
| Wed, 01 Jan 2014 16:00:00 |       16 |
| Thu, 02 Jan 2014 16:00:00 |       16 |
+---------------------------+----------+

-- Next, note that changing the session time zone does not influence the results

alter session set timezone = 'America/New_York';

select ts, hour(ts) from ts_test;

+---------------------------+----------+
| TS                        | HOUR(TS) |
|---------------------------+----------|
| Wed, 01 Jan 2014 16:00:00 |       16 |
| Thu, 02 Jan 2014 16:00:00 |       16 |
+---------------------------+----------+

In this case,

1. even though the time zone is America/Los_Angeles which is UTC -7, it only can insert +00:00 time zone.
2. when select the table the value is stored as America/Los_Angeles time zone, not +00:00.
3. modify the session time zone the select value is not changed.

So for points 1 and 2, when insert into databend timestamp we can ignore the zone.

But for point 3 I don't have good idea.

[TCeason]

@sundy-li

[sundy-li]

I think we can apply it via:

1. '2014-01-01 16:00:00' -> Use ctx's timezone to parse this datetime str and stores the wallclock as int64
2. '2014-01-02 16:00:00 +00:00' -> Use UTC timezone to parse this datetime str and stores the wallclock as int64