warning converting clover type: date to sql type: DATE

Support/help with CloverETL implementation problems

wills__aperio
Posts: 4
Joined: Mon Mar 13, 2017 11:27 pm
Location: Sausalito, CA

warning converting clover type: date to sql type: DATE

Postby wills__aperio » Thu Mar 16, 2017 6:26 pm

hi,

i'm trying to clear a warning in DBOutputTable..

i'm passing in data from a ComplexDataReader, my metadata has a field "lot_date" of type date, to a postgres table with a matching column. but i'm receiving a warning:

Invalid SQL query. Incompatible types - field lot_date, clover type: date, sql type: DATE


it's the same error message whether i map it via DB Table/Field mapping

Code: Select all

$account:=account;$lot_number:=lot_number;$lot_date:=lot_date;


or i just use the sql query

Code: Select all

INSERT INTO "tlh_test"."clover__import_table" ("account", "lot_number", "lot_date") VALUES ($account, $lot_number, $lot_date)


or

Code: Select all

INSERT INTO "tlh_test"."clover__import_table" ("account", "lot_number", "lot_date") VALUES ($account, $lot_number, ($lot_date)::date)


it actually runs correctly in all of the above configurations, but continues to warn me. any thoughts on how i can make it happy?
thanks!

designer v. 4.4.0.011
postgres v. 9.5

svecp
Posts: 25
Joined: Wed Nov 09, 2016 11:51 pm
Location: 2111 Wilson Blvd., Arlington VA 22201
Contact:

Re: warning converting clover type: date to sql type: DATE

Postby svecp » Fri Mar 17, 2017 1:51 am

Hi,

I came across exactly the same thing a while back, wondering what is wrong. The answer is actually in PostgreSQL documentation - PostgreSQL's DATE is a plain date information without any notion about the time. CloverETL's date is on the other hand essentially timestamp. The warning basically means, you're losing precision when you try to insert into a database.

So how to fix it? Easily, either set your database field to TIMESTAMP, or...

Open your metadata and select the field which is causing problems. On the right hand side of the dialog (when you scroll down a tiny bit) is a "Format" option. There is a heuristic which determines what kind of information given field is suppose to carry.

  1. If you leave it blank (i.e. set to default), it is basically exactly the same as you'd set format to DATE & TIME - engine will check against TIMESTAMP data type in your database
  2. If you set DATE only format, your database is going to be checked against DATE data type
  3. If you set TIME only format, your database is going to be checked against TIME data type

Hope this helps.
--
Pavel Švec | CloverETL | Sales Engineer | 2111 Wilson Blvd | Suite 320 | Arlington, VA 22201

wills__aperio
Posts: 4
Joined: Mon Mar 13, 2017 11:27 pm
Location: Sausalito, CA

Re: warning converting clover type: date to sql type: DATE

Postby wills__aperio » Fri Mar 17, 2017 5:13 pm

hi pavel,

thanks for the tip. the designer ui shows "yyyy-MM-dd" as the default setting in the metadata for the date's format so i assumed that was what it was going to do. as you say, when i actually changed it to "yyyy-MM-dd" the warning went away.

cheers!


cron