GREL Date Functions

Thad Guidry edited this page May 26, 2018 · 23 revisions

Date functions supported by the OpenRefine Expression Language (GREL)

See also: All GREL functions.

now()

Returns the current time.

toDate(o, boolean month_first / format1, format2, ... )

Returns o converted to a date object.

All other arguments are optional:

  • month_first: set false if the date is formatted with the day before the month.
  • formatN: attempt to parse the date using an ordered list of possible formats.

NOTE: See SimpleDateFormat for the complete format syntax.

Examples: You can parse the cells "Nov-09" and "11/09" using

value.toDate('MM/yy','MMM-yy').toString('yyyy-MM')

For a date of the form: "1/4/2012 13:30:00" use GREL function:

value.toDate('d/M/y H:m:s')

Here is the list of pattern letters:

Letter Date or Time Component Presentation Examples
G Era designator Text AD
y Year Year 1996; 96
Y Week year Year 2009; 09
M Month in year Month July; Jul; 07
w Week in year Number 27
W Week in month Number 2
D Day in year Number 189
d Day in month Number 10
F Day of week in month Number 2
E Day name in week Text Tuesday; Tue
u Day number of week (1 = Monday, ..., 7 = Sunday) Number 1
a Am/pm marker Text PM
H Hour in day (0-23) Number 0
k Hour in day (1-24) Number 24
K Hour in am/pm (0-11) Number 0
h Hour in am/pm (1-12) Number 12
m Minute in hour Number 30
s Second in minute Number 55
S Millisecond Number 978
n Nanosecond Number 789000
z Time zone General time zone Pacific Standard Time; PST; GMT-08:00
Z Time zone RFC 822 time zone -0800
X Time zone ISO 8601 time zone -08; -0800; -08:00

toString(o, optional string format)

When o is a date, format specifies how to format the date.

diff(date d1, date d2, string timeUnit)

For dates, returns the difference in given time unit (eg. "minutes", "hours", "days", "weeks", "months", "years").

Example:

diff(cells['date1'].value, cells['date2'].value, "days")

In case the value is negative you should invert date1 and date2 or multiply by -1.

inc(date d, number value, string unit)

Returns a date changed by the given amount in the given unit of time. Unit defaults to 'hour'.

For example, if you want to decrement a date by 2 months:

value.inc(-2,'month')

datePart(date d, string unit)

Returns part of a date. Data type returned depends on the unit. Units supported are:

Unit Date part returned Returned data type Example using [date 2014-03-14T05:30:04.000789000Z] as value
years Year Number value.datePart("years") -> 2014
year Year Number value.datePart("year") -> 2014
months Month Number value.datePart("months") -> 2
month Month Number value.datePart("month") -> 2
weeks Week (of the year) Number value.datePart("weeks") -> 2
week Week (of the year) Number value.datePart("week") -> 3
w Week (of the year) Number value.datePart("w") -> 3
weekday Day of the week String value.datePart("weekday") -> Friday
hours Hour Number value.datePart("hours") -> 5
hour Hour Number value.datePart("hour") -> 5
h Hour Number value.datePart("h") -> 5
minutes Minute Number value.datePart("minutes") -> 30
minute Minute Number value.datePart("minute") -> 30
min Minute Number value.datePart("min") -> 30
seconds Seconds Number value.datePart("seconds") -> 04
sec Seconds Number value.datePart("sec") -> 04
s Seconds Number value.datePart("s") -> 04
milliseconds Millseconds Number value.datePart("milliseconds") -> 789
ms Millseconds Number value.datePart("ms") -> 789
S Millseconds Number value.datePart("S") -> 789
n Nanoseconds Number value.datePart("n") -> 789000
nano Nanoseconds Number value.datePart("n") -> 789000
nanos Nanoseconds Number value.datePart("n") -> 789000
time Date expressed as milliseconds since the Unix Epoch Number value.datePart("time") -> 1394775004000
Clone this wiki locally
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.