I just learned currently Duckdb only support UTC (GMT) timezone as the default and the only one. It is fine when we always save timestamp in UTC in storage.

But when read them out by JDBC, we have a problem.

when call jdbc's DuckDBResultSet::getTimeStamp, it will first read out a String value like:

"2021-01-08 07:00:00.000" (which means UTC timezone, but it didn't add "Z" or "+00" at the end), which is actually "2021-01-08 15:00:00.000+08" for my local time.

When for this string, duckdb jdbc will use

Timestamp.valueOf(getLazyString(columnIndex))

to convert it to timestamp. But this function didn't consider what is the defaultTimeZone. So, the retrieved Timestamp will lost 8 hours for my case. For MySQL or Postgres, it will convert the timestamp to local timezone.

So, I have to temporarily solve this be change my code like:

if (isDuckdb) {
  String value = ((DuckDBResultSet)rs).getLazyString(i + 1);
   if (value != null) {
     // adjust timezone shift
     timestamp = new Timestamp(Timestamp.valueOf(value).getTime() + TimeZone.getDefault().getRawOffset());
   }
}

This can ensure the retrieved timestamp is correct. But I still have many other cases I can not handle, for example, if I want to query the total revenue of yesterday, if I write:

select sum(revenue) from orders where order_ts >= '2021-01-07' and order_ts < '2021-01-08'

Then, I will get wrong number, since the string constants '2021-01-07' will be actually UTC timezone.

Seems not only JDBC have this problem, but also Python API (pandas fetchdf)

I know this may related with #561 , but that issue mostly related with the general and full support of "timestamp with time zone". But as an embedded database, maybe we can just support use the system's default timezone for all time related functions/operations, and we can still save those data in UTC time, but when we translate user inputed date string, or get out timestamp, we automatically convert to user's specified timezone. This seems is all my need, and is less complicated than full support of "timestamp with time zone".

I'm very looking forward to know what it your plan for timezone support (whether is in roadmap), Thanks a lot!