topic [resolved] Convert AD timestamp to date string in Talend Studio

[resolved] Convert AD timestamp to date string

Anonymous — Wed, 19 Dec 2012 02:49:28 GMT

Can anyone point me in the right direction to convert an AD timestamp (which is a numeric string stored as the number of 100ns periods since 1/1/1600, the lastLogon value) to a date string, I'm doing this in a tmap
I have lastLogon=129052436323349513 and want "2012/01/01 14:00:00" (No, that isn't the right string for that lastLogon value, but that's the format I want.)
I have just spent the last 3 hours frustratedly googling for help and can't find anything that doesn't seem to require a lot more java knowledge than I have, as I'm reasonably adept at Talend but a complete newby wrt to java programming.
I was hoping to do something like:
TalendDate.formatDate("yyyy-MM-dd HH:mm:ss", TalendDate.addDate(TalendDate.parseDate("yyyy/MM/dd HH:mm:ss", "1970/01/01 00:00:00), (Long.valueOf(Var.var9).longValue())/10000000-109207*24*3600,"ss"))
Which I think would be right except TalendDate.addDate only wants an int, not a long.
Happy to learn, but currently ready to throw my laptop through a window.

Re: [resolved] Convert AD timestamp to date string

Anonymous — Wed, 19 Dec 2012 04:08:46 GMT

I found a solution, and learned something new about java dates.
My tmap now has: new Date(TalendDate.parseDate("yyyy/MM/dd", "1970/01/01").getTime() +(Long.valueOf(row1.lastLogon).longValue()/10000-11644473600000L+36000000L))
10000 to convert 100ns time intervals to milliseconds, 11644473600000L as the number of milliseconds between 1/1/1601 and 1/1/1970 (wolfram alpha says 134774 days, which is 11,644,473,600s) and the 36000000L (10 hours in milliseconds) to correct for my timezone.
The output values agree with AD

Re: [resolved] Convert AD timestamp to date string

Spretorius — Mon, 07 Jan 2013 13:46:53 GMT

Hi magpac
Just want to let you know that you saved me alot of time by providing your solution
So I am very gratefull thanks!
Started to get frustrated and then came across this.
Regards,