Conversion from Unix timestamp to Date

Hello to everybody here,
complete Newbie having a bit trouble with the conversion of the Unixtimestamps to a normal date. My file csv Input file contails Unixtimestamps in seconds like:
1315922016

which i would need to convert into a date like:
20.9.2011 13:43:39

Nothing i could think of my own seemd to be working. Then I found the post here in the forum saying i should use the Date&Timeshift node. When i read in the csv i get

First problem now is, that Col0 is the right column but i can’t chose it in the top section (which is a problem i often had while playing around with data a bit), which is quite odd to me. Why can’t i chose whichever column i see fit?

When i pick “Use numerical” with granularity “seconds” on the other hand nothing is beeing transformed values stay as they are.

How do i do this right in Knime?

Have a good one!

Hi there,

you can use the node UNIX Timestamp to Date&Time (admittedly, I didn’t know that it existed until now, thanks to NodePit) to convert the timestamp to a Date/Time cell. The node expects a “Number” input, so you probably need to prepend it with a String to Number node.

Here’s an example workflow in action:

https://nodepit.com/workflow/com.nodepit.space%2Fqqilihq%2Fpublic%2Fforum%2Fconversion-from-unix-timestamp-to-date-21737.knwf

Hope this helps!
Philipp

5 Likes

Hi qqilihq,
thanks for you answer i am going to check this out.
For now: Do you know why i can’t chose in the Top section any columns?
This would interest me on its own sake, since i seem to not understand how the program works / is a problem i might run into a lot later on.

Ok checked out your link,
unfortunately Unix Timestamp to Date&Time is one of the things i tried earlier. I couldn’t get it to work how i need the output date.

There is only one option for Date and Time which produces an output like this:
Output

but as mentioned i would need this format:
20.9.2011 13:43:39

And i didn’t know how to get there.
PS That would be a cool enhancement for a future version that you could chose more dateformats than just one. Especially since the offered date format is not very “clean” and a lot of programms need a daterformat without any letters in it.

Part of the problem here is that you’re confusing ideas. The are two things here:

  • the data
  • the display

The data is the time and date fixed (if we looked down deep enough, likely being stored as a long or double numeric value;) the display is just some text being rendered in a form that you don’t like seeing. Depending on how you’re getting this data to the other ‘programs that need a date format like …’ this has nothing to do with you’re seeing the data rendered in that column. That will be an issue about what Writer node you are using to get data out of KNIME into whatever format those other programs will use.

There is a limited supply of alternative styles to render that string differently within KNIME in the preferences:

1 Like

OK i wanted to give a quick update and in case somebody has the same questions.
I was able to get this done by doing this in 2 steps:

Workflow

The trick was to first create one column with just the date and then in the second node just the time and then the Letter “T” between Date and Time disappeared which caused the problem to import this into another program.

The Question why some columns where not available was in hindsight easy, if a node demands a certain data type, then only columns with the according data type are shown. Didn’t see this at first. :upside_down_face:

The only thing is that my solution is not very elegant due to:

  1. 2 nodes that are waiting for each other for this actually one task

  2. The Date and time is not in one colum, which was fine for my goal today but generally a method where one could chose to write it in one column would be even better.

So if someone has a more elegant solution for this i would still be interested in hearing it

@Phillip: Even that i had to do it different thanks for posting your workflow!! It was useful because it showed me the Node Pit, and create Table which is good to know.

@Quaeler: I don’t understand you 100% since your version (mac i assume) looks pretty different than on windows. What i included in my post, was the actual output (well i included the preview), but the csv writer module did write this exactly like this to the csv file so it was not related to settings/preferences how to view the data. But maybe you had a different writer module in head with different capabilities?

Nice evening you all and really thanks for your Input. Appreciate it!!

Hi there @TotalDataLoss,

welcome to KNIME Community and KNIME itself!

Indeed writing Date&Time column with CSV Writer is as seen (rendered) in KNIME. To have your specific format in this case (and not separate columns holding date and time information) you would need to use Date&Time to String node. There you can define your preferred format which will be output in string column. I know it is still two nodes (but more straightforward at least) but don’t see a way to go from UNIX timestamp into your desired format without scripting (Java, Python?).

Others programs @quaeler was referring are for databases for example. When you get Date&Time column from KNIME to Date&Time column in some database should work normally (no ‘T’ and rendered as in db format).

Hope this helps!

Br,
Ivan

1 Like

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.