Pentaho

 View Only

 Select current datetime from Database instead of PDI server time using Table Input step

  • Pentaho
  • Pentaho
  • Pentaho Data Integration PDI
Rajkumar Venkatasamy's profile image
Rajkumar Venkatasamy posted 10-11-2021 12:04

Hi Pentaho Community users,

 

I am using PDI community edition version 9.0. The table input step has a simple sql query pointed to postgres database connection and the statement is as shown below:

 

select now()

I expected that PDI will execute the query in the target database (which is running in Pacific time) and return current date time of database server (which is Pacific time). However PDI is returning the timestamp in UTC since the PDI server is running in UTC timezone.

 

I can see from database server logs that the PDI is issuing the sql command (select now() to Postgres DB but still the response that I get is in PDI server timezone instead of database timezone. Why is this happening ? and how can I make PDI to return database server time instead of PDI server timezone ?

 

 

Thanks and Regards

Rajkumar.V

Ana Gonzalez's profile image
Ana Gonzalez posted 10-13-2021 09:58

This is probably more a Postgres question than a PDI question, and it probably has to do with how Postgres handle connections. I don't know about Postgres, but in Oracle you have DB locale settings and Connection locale settings, and this seems like in Postgres is similar.

Probably there's some kind of parameter you can add to your connection information to change the timezone of your connection.

Alberto Garcia's profile image
Alberto Garcia posted 10-25-2022 08:01
Actually, it does have to be with PDI.
One way of setting it, it's going to your spoon.sh / spoon.bat file, and adding this argument to the java options.
In my case, in Ubuntu, I added this "-Duser.timezone=UTC" at the end of the "OPT" variable declaration.

WARNING: This will set the timezone for all of your PDI executions. Every single Timezone / Date you retrieve from your database or files will be converted to that timezone unless you do some additional timezone conversion within PDI.

EDIT: For the spoon.bat file in windows, you need to add it like this:  (appending a space and the extra argument between "")
Petr Prochazka's profile image
Petr Prochazka posted 10-25-2022 09:15
Hi Rajkumar,
function now() or CURRENT_TIMESTAMP returns time in client timezone. use LOCALTIMESTAMP for gets local time of DB server.

More info is in PostgreSQL docs.

Tested on client with Europe/Prague, server is started with Europe/London time zone.
postgres=# SELECT now(), CURRENT_TIMESTAMP, LOCALTIMESTAMP;
              now              |       current_timestamp       |       localtimestamp
-------------------------------+-------------------------------+----------------------------
 2022-10-25 14:14:53.043397+01 | 2022-10-25 14:14:53.043397+01 | 2022-10-25 14:14:53.043397
(1 řádka)

Related Content