agavra commented on code in PR #9480:
URL: https://github.com/apache/pinot/pull/9480#discussion_r985374412
##########
pinot-common/src/main/java/org/apache/pinot/common/function/scalar/DateTimeFunctions.java:
##########
@@ -704,4 +707,24 @@ public static long timestampDiff(String unit, long
timestamp1, long timestamp2)
ISOChronology chronology = ISOChronology.getInstanceUTC();
return DateTimeUtils.getTimestampField(chronology,
unit).getDifferenceAsLong(timestamp2, timestamp1);
}
+
+ /**
+ * For timestamp with time zone, the internally stored value is always in
UTC (Universal Coordinated Time,
+ * traditionally known as Greenwich Mean Time, GMT). This method will
transform that timestamp to a timestamp
+ * that represents the millis since epoch in the specified time zone.
+ *
+ * @return a timestamp that corresponds to the {@code timestampInUTC} at
{@code timeZone}
+ */
+ @ScalarFunction
+ public static Timestamp atTimeZone(long timestampInUTC, String timeZone) {
Review Comment:
I agree it's a bit tricky - in Postgres it returns a `Timestamp WITH TIME
ZONE` type, which maybe we need to add to make this work seamlessly. You can
see the following example:
```sql
CREATE TABLE foo (ts TIMESTAMP);
INSERT INTO foo (ts) VALUES (TIMESTAMP '2022-10-23 09:00:00')
--- you can see that the default behavior returns what we stored
--- which is, by default, UTC time
SELECT ts FROM FOO;
ts
------------------------
2022-10-23T09:00:00Z
------------------------
--- if I ask for it "interpreted as a PST time" then I'll get
--- the UTC timestamp for the corresponding datetime in PST
SELECT ts AT TIME ZONE 'PST' FROM foo;
ts
------------------------
2022-10-23T17:00:00Z
------------------------
--- this shows the degenerate case, when I say that the original
--- timestamp I stored is in 'UTC', but I actually want the timestamp
--- returned to me with what that corresponds to in 'PST'
SELECT ts AT TIME ZONE 'UTC' AT TIME ZONE 'PST' FROM foo;
ts
------------------------
2022-10-23T01:00:00Z
------------------------
```
It's all very confusing!
I thought about whether having this type or not is a problem, and I'm not
sure that it is if we always store the timestamp as `epochMillis`. The only
difference is that when we introduce `AT TIME ZONE` types, we would just need
to convert the type _before_ we store anything (as opposed to assuming that the
timestamp that we are given is in UTC). See the difference in behavior for
Postgres:
```sql
CREATE TABLE foo (ts TIMESTAMP);
INSERT INTO foo (ts) VALUES (TIMESTAMP '2022-10-23 09:00:00' AT TIME ZONE
'PST')
SELECT ts FROM foo;
ts
------------------------
2022-10-23T17:00:00Z
-----------------------
```
vs
```sql
CREATE TABLE foo (ts TIMESTAMP WITH TIME ZONE);
INSERT INTO foo (ts) VALUES (TIMESTAMP '2022-10-23 09:00:00' AT TIME ZONE
'PST')
SELECT ts AT TIME ZONE 'PST' FROM foo;
ts
-----------------------
2022-10-23T09:00:00Z
-----------------------
```
I'll make sure to double check that all the behavior with this PR is as
expected, but I think it should be.
(FWIW, in SQL the `DateTime` type is generally considered identical to
`Timestamp` - https://www.postgresql.org/docs/current/datatype-datetime.html)
##########
pinot-common/src/main/java/org/apache/pinot/common/function/scalar/DateTimeFunctions.java:
##########
@@ -704,4 +707,24 @@ public static long timestampDiff(String unit, long
timestamp1, long timestamp2)
ISOChronology chronology = ISOChronology.getInstanceUTC();
return DateTimeUtils.getTimestampField(chronology,
unit).getDifferenceAsLong(timestamp2, timestamp1);
}
+
+ /**
+ * For timestamp with time zone, the internally stored value is always in
UTC (Universal Coordinated Time,
+ * traditionally known as Greenwich Mean Time, GMT). This method will
transform that timestamp to a timestamp
+ * that represents the millis since epoch in the specified time zone.
+ *
+ * @return a timestamp that corresponds to the {@code timestampInUTC} at
{@code timeZone}
+ */
+ @ScalarFunction
+ public static Timestamp atTimeZone(long timestampInUTC, String timeZone) {
Review Comment:
I agree it's a bit tricky - in Postgres it returns a `Timestamp WITH TIME
ZONE` type, which maybe we need to add to make this work seamlessly. You can
see the following example:
```sql
CREATE TABLE foo (ts TIMESTAMP);
INSERT INTO foo (ts) VALUES (TIMESTAMP '2022-10-23 09:00:00')
--- you can see that the default behavior returns what we stored
--- which is, by default, UTC time
SELECT ts FROM FOO;
ts
------------------------
2022-10-23T09:00:00Z
------------------------
--- if I ask for it "interpreted as a PST time" then I'll get
--- the UTC timestamp for the corresponding datetime in PST
SELECT ts AT TIME ZONE 'PST' FROM foo;
ts
------------------------
2022-10-23T17:00:00Z
------------------------
--- this shows the degenerate case, when I say that the original
--- timestamp I stored is in 'UTC', but I actually want the timestamp
--- returned to me with what that corresponds to in 'PST'
SELECT ts AT TIME ZONE 'UTC' AT TIME ZONE 'PST' FROM foo;
ts
------------------------
2022-10-23T01:00:00Z
------------------------
--- just showing types
SELECT pg_typeof(ts), pg_typeof(ts AT TIME ZONE 'PST') FROM foo;
pg_typeof | pg_typeof
-----------------------------------------------------------
timestamp without time zone | timestamp with time zone
-----------------------------------------------------------
```
It's all very confusing!
I thought about whether having this type or not is a problem, and I'm not
sure that it is if we always store the timestamp as `epochMillis`. The only
difference is that when we introduce `AT TIME ZONE` types, we would just need
to convert the type _before_ we store anything (as opposed to assuming that the
timestamp that we are given is in UTC). See the difference in behavior for
Postgres:
```sql
CREATE TABLE foo (ts TIMESTAMP);
INSERT INTO foo (ts) VALUES (TIMESTAMP '2022-10-23 09:00:00' AT TIME ZONE
'PST')
SELECT ts FROM foo;
ts
------------------------
2022-10-23T17:00:00Z
-----------------------
```
vs
```sql
CREATE TABLE foo (ts TIMESTAMP WITH TIME ZONE);
INSERT INTO foo (ts) VALUES (TIMESTAMP '2022-10-23 09:00:00' AT TIME ZONE
'PST')
SELECT ts AT TIME ZONE 'PST' FROM foo;
ts
-----------------------
2022-10-23T09:00:00Z
-----------------------
```
I'll make sure to double check that all the behavior with this PR is as
expected, but I think it should be.
(FWIW, in SQL the `DateTime` type is generally considered identical to
`Timestamp` - https://www.postgresql.org/docs/current/datatype-datetime.html)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
