2

I am using Teradata JDBC 16.20 in Datagrip. Whenever I try to do anything with date, it returns 1 day less. For instance: SELECT date'2017-08-01' returns 2017-07-31 in Datagrip and in Teradata SQL Assistant it returns correctly 01/08/2017.

Does anyone know why?

VeilEclipse
  • 2,461
  • 7
  • 30
  • 49
  • If it helps, this must be a Datagrip issue. I just ran `SELECT date'2017-08-01'` through JDBC 16.20 Driver against my 15.10 database and it returned as expected. – JNevill Feb 06 '18 at 19:41
  • Might be based on different Time Zones. `SELECT CAST (date'2017-08-01' AS TIMESTAMP AT LOCAL), CAST (date'2017-08-01' AS TIMESTAMP AT 0)` – dnoeth Feb 07 '18 at 10:19
  • @dnoeth that seems to work. thanks very much – VeilEclipse Feb 08 '18 at 01:41
  • @dnoeth: If I CAST date as TIMESTAMP, it works well but if I just return DATE or CAST TIMESTAMP as DATE, it returns 1 day less. – VeilEclipse Jul 12 '18 at 01:02

2 Answers2

1

We have the same issue using DataGrip with a Vertica database. My hunch is that the date is being shifted twice. When I select current_timestamp (which on Vertica is timestamp with timezone) the date is correct (typically one day earlier than the current date), but when I select current_date, I get the previous day (my timezone is US/Pacific). What I think is happening is the JDBC driver is adjusting the date based on the two timezones, and then DataGrip is then adjusting it a second time.

We don't have issues using the same Vertica database with the same JDBC driver, but a different SQL Client (DbVisualizer).

The only workaround I can offer using DataGrip is to install a much older version. DataGrip 2016.3.4, Build #DB-163.13906.13, built on February 21, 2017 seems to handle date columns correctly.

user3458206
  • 11
  • 1
  • This is why I haven't upgraded from 2017.2.2. My new coworker downloaded a newer version and has to deal with this issue. – Gary Oct 03 '19 at 19:52
1

Adding -Duser.timezone=UTC to VM options seems to solve the problem.

VeilEclipse
  • 2,461
  • 7
  • 30
  • 49