devanbenz commented on issue #12218:
URL: https://github.com/apache/datafusion/issues/12218#issuecomment-2341428478
@findepi @alamb It actually appears that datafusion (and in regards apache
arrow) seems to be casting as expected afaik. It actually looks like the
`now()` udf in datafusion is incorrect (or inconsistent with how other engines
do it). Taking a look at 2 other systems `Postgres` and `Clickhouse` I'm seeing
that when calling `now()` without any adjustments it returns the local TZ
timestamp and not UTC. It appears that datafusion is returning UTC time. Not
sure if this is by design.
Postgres
```
postgres=# SELECT now();
now
-------------------------------
2024-09-10 11:22:41.895841-05
(1 row)
postgres=# SELECT now()::timestamp;
now
----------------------------
2024-09-10 11:22:47.294941
(1 row)
postgres=# SELECT now()::timestamptz;
now
-------------------------------
2024-09-10 11:22:52.118688-05
(1 row)
```
Clickhouse
```
:) SELECT now();
SELECT now()
Query id: 13a227b8-f867-49ad-9941-8d8bb70b9c92
┌───────────────now()─┐
1. │ 2024-09-10 11:25:49 │
└─────────────────────┘
1 row in set. Elapsed: 0.005 sec.
:) SELECT now()::timestamp;
SELECT CAST(now(), 'timestamp')
Query id: adb042b8-7ffb-4e98-bbb4-21affade7201
┌─CAST(now(), 'timestamp')─┐
1. │ 2024-09-10 11:25:56 │
└──────────────────────────┘
1 row in set. Elapsed: 0.002 sec.
```
Datafusion
```
> SELECT now();
+-----------------------------+
| now() |
+-----------------------------+
| 2024-09-10T16:26:39.928247Z |
+-----------------------------+
1 row(s) fetched.
Elapsed 0.007 seconds.
> SELECT now()::timestamp;
+----------------------------+
| now() |
+----------------------------+
| 2024-09-10T16:26:44.623198 |
+----------------------------+
1 row(s) fetched.
Elapsed 0.010 seconds.
> SELECT now()::timestamptz;
+-----------------------------+
| now() |
+-----------------------------+
| 2024-09-10T16:26:52.911837Z |
+-----------------------------+
1 row(s) fetched.
Elapsed 0.006 seconds.
```
Not that all these queries were ran at around 11:30 AM CST. The PG and CH
queries are returning my local TZ where as DF does not and uses Zulu time
instead. *Note: Clickhouse does not support timestamptz*
--
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]
