I have a column with dates in the form "19 May 2026 at 18:55:11 BST" but I can't seem to work out the right way to transform this into a date with the toDate() function. Any hints? Also, the documentation for toDate() has some odd characters where it says
"...can be parsed into a date using value.toDate('d/M/y H:m:s')"
Is this intentional or is it an error in the documentation?
Assuming that in your example "BST" stands for British Summer Time (and not for Bangladesh Standard Time) I believe I could get it to work with
value.replace(" at", "").replace(" BST", "").toDate().inc(-1, "hours")
I would have thought that something like
value.replace(" at ", " ").replace("BST", "+0100").toDate("d MMM y H:m:s Z")
should work, too, and am not sure why it doesn't. Maybe someone else can explain that?
The characters in the documentation are Html entities; read ":" for '':"
I think you can do this using 'toDate' directly with the GREL:
value.toDate("dd MMM yyyy 'at' hh:mm:ss z")
Quoting 'at' in single quotes (inside the double quotes for the string format) tells the converter to treat that as a straight string, and the lowercase z rather than uppercase Z tells it to interpret the final letters as the timezone
Even better and way simpler! I also figured out why it did not work out on my first try: I tried it on my local installation and this (e.g. month names) is obviously locale-dependent.
value.toDate("en", "dd MMM yyyy 'at' hh:mm:ss z")
Did work for me...
That definitely looks like an error. As @r0man-ist says, these should just be straight colons rather than the HTML code value :
Thanks this mostly works, but not for dates like "7 March 2026 at 3:59:21 GMT+0" which I have in the same dataset - I can't seem to find a format letter that likes "GMT+0" .
Also, I think the documentation could do with mentioning the single quote string escape mechanism as that is very handy. The documentation also doesn't explain why you need three Ms to describe a full month name (the table actually suggests a single one should suffice, but it doesn't).
Finally, why is there no "Custom timeline facet..." option like there is for text and numeric facets? If I just want a timeline facet on a string column (like I currently have), then I have to either convert the column first, or else make a (broken) timeline facet and then click "change" and modify the facet formula.
It seems that "GMT+0" doesn't register as a valid match for the lowercase z in the function. While specifying an offset to the timezone is valid, it looks like it has to be in the form 00:00.
So 7 March 2026 at 3:59:21 GMT+0 doesn't work with the formula but 7 March 2026 at 3:59:21 GMT+00:00 will work. I can't immediately see a way around this except doing some additional manipulation to the starting string - basically similar to the approach suggested by @r0man-ist in the thread. There are going to be lots of ways of approaching this and the best approach might depend on what patterns there are across the data. If you know the only non-valid values are exactly GMT+0 you could do a simple value.replace("GMT+0","GMT+00:00"). However if there are more variations then something more generaly like value.replace(/([A-Z]{3}(+|-))(\d)$/,"$10$3:00") might work
This seems like a good suggestion. I do remember having quite a bit of discussion about the documentation for this function when we did the project to improve the OpenRefine docs several years ago (that project established the basis of the current documentation) and it was difficult to agree how much detail should be included here because the potential combinations are very extensive and are already documented (but in documentation aimed at developers) at SimpleDateFormat (Java Platform SE 8 ) which is (as far as I remember it) the underlying function used by the code
The M or MM vs MMM or MMMM differentiates between the use of digits for the month (M or MM) and spelt out months (MMM or MMMM)
I think the answer is simply it was never implemented and the work around of adding a timeline facet then clicking "Change" to modify the formula is a relatively easy work around. I agree its an inconsistency which would be good to eliminate (although I'm tempted to say that it would be nicer if you could simply modify the facet display mode independently of the expression used for the formula - this approach could simplify the menu options as well I think)
In terms of the options you describe, the "nice" aspect of doing the timeline facet with many non-datetime entries is by modifying the expression and then using the facet checkboxes for Error or Non-time you can easily see which values are escaping your current expression in terms of conversion
Great, thank you. Maybe a link to that Java description would be good to have in the documentation. Who knew that even machine-created dates could be messy 
?
Happily the link to the java documentation is already in the OpenRefine docs (both in the online documentation and in the toDate documentation in the expression Help tab in the app
Oh, so it is, sorry I missed that.
A little searching suggests that SimpleDateFormat is considered 'legacy', and if instead we used a ZonedDateTime object then we could use an "O" token for that GMT+0 part (DateTimeFormatter (Java Platform SE 8 )). Is it perhaps time to update the date parsing?
A few semi-random additional notes to add to what's been said already:
- while toString() switched to DateTimeFormatter, toDate() still uses SimpleDateFormat for parsing, even though we no longer use java.util.Date. There's no good reason for this inconsistency from the 2018 date fiasco.
- the in-app help currently points to the Java 7 definition (and the website the Java 8), but we're on Java 11 for OpenRefine 3.10 (and going to Java 21 for the next non-patch release), so the correct link is https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/text/SimpleDateFormat.html
- the internal date format is the equivalent to the Java Instant (but stored as a bastardized OffsetDateTime with the offset coerced to 00:00) which means everything is converted to UTC and any timezone info is lost
- Three letter timezone codes like "BST" are deprecated, specifically because they are duplicated. The long equivalent is "Europe/London". GMT/Zulu time doesn't have a Daylight Savings Time / Summer Time and is constant year round.
The next major release will support some additional date-related data types that were introduced in java.time for Java 8, including ZonedDateTime but likely not ChronoLocalDate for Hijrah, Japanese, Mingo, Thai Buddhist calendars. We can use that opportunity to clean up some of the rest of this messiness.
Tom
Great context, thanks Tom! Yes sounds like a worthy area for some updating work (no pun intended 
). Incidentally, in the UK we do use BST/GMT for summer/not-summer time zone designations, though GMT is often used synonymously with UTC (e.g. calling BST "GMT+1").