topic Re: Help in parsing TimeStamp from GlobalVar to Query in Talend Studio

Help in parsing TimeStamp from GlobalVar to Query

HJlassi1619437866 — Sat, 16 Nov 2024 00:13:16 GMT

Hello,

I want to stock a date (timestamp) in a variable to use it late in a query (PostgreSQL). so i used tsetGlobalVar and then in my query i used this query :

WHERE SX.\"Date\" = "+TalendDate.parseDate("yyyy-MM-dd'T'HH:mm:ss", (String)globalMap.get("row2.DateKey"))+"

And i get a java.lang.NullPointerException Error

I think the problem is in parsing Timestamp, please can anyone help me

Re: Help in parsing TimeStamp from GlobalVar to Query

gjeremy1617088143 — Mon, 26 Apr 2021 12:57:19 GMT

Hi, maybe you have to check before if your date is null ?

Re: Help in parsing TimeStamp from GlobalVar to Query

HJlassi1619437866 — Mon, 26 Apr 2021 12:58:33 GMT

Thank you for your reply my date is not null

Re: Help in parsing TimeStamp from GlobalVar to Query

gjeremy1617088143 — Mon, 26 Apr 2021 13:05:01 GMT

as i see you set your value to "MaxDate", so you try to parse a string("MaxDate") as a date try this instead : row2.MaxDate without the double quote in your tsetglobalvar value

Send Me Love and Kudos

Re: Help in parsing TimeStamp from GlobalVar to Query

gjeremy1617088143 — Mon, 26 Apr 2021 13:07:44 GMT

(String)globalMap.get("row2.DateKey")) don't exist in your globalvar : (String)globalMap.get("DateKey")) exist.

Re: Help in parsing TimeStamp from GlobalVar to Query

gjeremy1617088143 — Mon, 26 Apr 2021 13:12:27 GMT

So first check wich field contain the date you want in the row2 : then in your tsetglobalvar : you set a key here "DateKey" and (the value row2.(the field of the date) without double quote).

then you can call DateKey : (String)globalMap.get("DateKey")). Don't use parseDate you can already send a string in the query.

Re: Help in parsing TimeStamp from GlobalVar to Query

HJlassi1619437866 — Mon, 26 Apr 2021 13:22:46 GMT

Thank you for you reply i've updated my job as you asked me to do,

unfortunately, it doesn't work because talend is parsing data from timestamp to date, Now i get this error :

Syntaxe error near "Mar"

My date is : 2021-03-17T11:40:54

Talend reading it as : Wed Mar 17

Please can you help me with that, FYI : im using postgresql

Re: Help in parsing TimeStamp from GlobalVar to Query

gjeremy1617088143 — Mon, 26 Apr 2021 13:25:30 GMT

ok, so in your row 2 your date field is a date type or a string ? IF i'ts a date set "yyyy-MM-dd'T'HH:mm:ss" in the pattern of the date on the suiviXML component schema.

The tDbinput already parse the date with a pattern so you can put your own directly on his schema and it will overwrite it

Re: Help in parsing TimeStamp from GlobalVar to Query

HJlassi1619437866 — Mon, 26 Apr 2021 13:28:04 GMT

Yes it's already set as a date and pattern is like you said

Re: Help in parsing TimeStamp from GlobalVar to Query

HJlassi1619437866 — Mon, 26 Apr 2021 13:28:21 GMT

Yes

Re: Help in parsing TimeStamp from GlobalVar to Query

gjeremy1617088143 — Mon, 26 Apr 2021 13:37:51 GMT

and you can use TalendDate.formatDate("yyyy-MM-dd'T'HH:mm:ss",row2.MaxDate) in your globalvar value

then you put it as a string in the query with (String)globalMap.get("DateKey")

Re: Help in parsing TimeStamp from GlobalVar to Query

gjeremy1617088143 — Mon, 26 Apr 2021 13:42:28 GMT

you can also use to_char((your date value)::timestamp, 'yyyy-MM-dd'T'HH:mm:ss') in your postgre select to convert it directly to string