0

the problem is as follows:

The file that i receive has a timestamp like

06/10/2016 02:58 AM
06/10/2016 05:20 PM
etc.

I'm using pentaho/kettle to eventually put this into a mysql database, the problem is that mysql only accepts yyyy-mm-dd hh-mm-ss timestamps. I'm not that great(if at all) in JavaScript and Kettle only support javascript, sadly.

I got it all split up, so it has a column called, 'days','months,'years', etc.

How can I make it so that if column Am/pm has "pm" in it, that it add 12 to the 'hours' column?

Or is there an easier way to do this in Kettle?

EDIT:

as i receive the csv i have 5 columns

1. Timestamp    2. Mobil_origin    3. mobile_Dest.     4.Call_cit     5.Call_dur

I want it so that all the timestamps get 'corrected' to the yyyy-mm-dd hh-mm-ss format.

so instead of 06/10/2016 05:20 PM it has to show 2016-10-06 17:20:00

It's the only thing that needs to happen actually, the rest of the file is good as is to be exported to mysql.

mplungjan
  • 169,008
  • 28
  • 173
  • 236
DevOps
  • 384
  • 1
  • 12
  • 1
    I would suggest using the native EPOCH timestamp which is milliseconds since 1970, if you use a human readable timestamp then you have to consider time-zones. Its also a lot easier to add and subtract time to a since millisecond value. – SPlatten Jan 15 '19 at 15:58
  • 2
    `Date.parse("06/10/2016 05:20 PM")` will do just that – mplungjan Jan 15 '19 at 16:02
  • @mplungjan Can I do that so it does that for every entry? – DevOps Jan 15 '19 at 16:05
  • Sure. I do not know when you need to do it because you did not post that part, but you can likely loop over some selector – mplungjan Jan 15 '19 at 16:09
  • @mplungjan Okay, i'll edit my question – DevOps Jan 15 '19 at 16:10
  • 1
    Parse it as @mplungjan said, then [convert it back to a formatted string](https://stackoverflow.com/questions/20083807/javascript-date-to-sql-date-object). To make your life easier you can use [luxon](https://moment.github.io/luxon/docs/) with `dt.toSQL()`. – Cinn Jan 15 '19 at 16:12

0 Answers0