How to convert date string back to unix time with GREL (OpenRefine)

Question

I'm working with a large CSV file with millions of rows. I'm using OpenRefine to manipulate this large data set.

I have a column with date strings in this format "2017-08-17 04:36:00". And I would like to convert them to Unix time in integer format like 1502944560.

I see many Q&A on converting Unix time to Date String but not the other way around. Is this possible in OpenRefine?

score 4 · Answer 1 · answered May 15 '20 at 07:03

4

value.toDate().datePart("time")

(see the bottom of this documentation for other conversion strings)

answered May 15 '20 at 07:03

Ettore Rizza

2,800
2
11
23

I actually tried it but didn't convert `toDate()` first. Thanks! – Scott Yu May 15 '20 at 07:10
There is probably an issue with your string. Could you copy paste the exact content of one of your cells? – Ettore Rizza May 15 '20 at 07:13
1

Sorry, I miscommunicated. It's working! Thanks for saving me bunch of time. – Scott Yu May 15 '20 at 07:26
1

It's a common mistake. The `datePart()` method only works on an object of type 'date', not on a string that looks like a date. ;) – Ettore Rizza May 15 '20 at 07:29
2

Added to our official FAQ on OpenRefine Wiki https://github.com/OpenRefine/OpenRefine/wiki/Recipes#how-to-convert-date-string-back-to-unix-time – Thad Guidry May 15 '20 at 15:45

How to convert date string back to unix time with GREL (OpenRefine)

1 Answers1