You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 16 Next »

Functions overview

String based date time functions

Basic date time functions works with date time as Strings.

On this page:

Function

Desctipyion

String actualDate()

Returns the current date in dd.MM.yyyy format. This function is suitable also for inducting an current date for periodic import from data source which has no time column.

String actualDate(String format)

Works like the actulaDate() function extended with custumizable output date format. See section Date and time patterns. Example: 

 return actualDate('yyyy-MM-dd');//returns date as 2011-12-15

String datetimePart(String value, String part)

Returns the part of the passed date or time string. This string should be in format yyyy-MM-dd HH:mm:ss or yyyy-MM-dd. The part parameters defines one of the following: year, monthOfYear, weekOfYear, dayOfMonth, dayOfWeek, dayOfYear, hourOfDay, minuteOfHour, secondOfMinute

Working with date time objects

Before you can use advanced functions, the date time information must be converted to one of the following objects:

  • LocalDate - represents the date without time
  • LocalTime - represents time only
  • DateTime - represents date and time including the time zone

Function

Desctipyion

LocalDate date(String value)

Converts the passed value to LocalDate object. The value must be in on of the following formats: dd.MM.yyyy, dd/MM/yyyy, yyyy-MM-dd. Example: 

 
def a = date('2011-01-01')
def b = date(value(1))

LocalDate date(String value, String format)

Works in the same way as date(String value), additionally you can specify the format of the value parameter. See section Date and time patterns.

LocalTime time(String value)

Converts the passed value to LocalTime object. The value must be in HH:mm:ss format. Example:

def a = time('11:00:23')
def b = time(value(1))

LocalTime time(String value, String format)

Works in the same way as time(String value), additionally you can specify the format of the value parameter. See section Date and time patterns.

DateTime datetime(String value)

Converts the passed value to DateTime object. The value must be in on of the following formats: dd.MM.yyyy HH:mm:ss, dd/MM/yyyy HH:mm:ss, yyyy-MM-dd HH:mm:ss. Example: 

 
def a = datetime('2011-01-01') //creates date time object with 2011-01-01 00:00:00 value
def b = datetime('2011-01-01 23:32') //creates date time object with 2011-01-01 23:32:00 value
def c = datetime('11:21:33') //creates the date time object with 1970-01-01 11:21:33 value

DateTime datetime(String value, String format)

Works in the same way as datetime(String value), additionally you can specify the format of the value parameter. See section Date and time patterns.

Basic functions

After the date time has been converted into appropriate objects, you can use following functions:

Function

Desctipyion

Integer year(LocalDate date)

Extracts the year of the LocalDate object. Example:

def a = date('2011-01-01')
return year(a);//returns 2011

Integer month(LocalDate date)

Extracts the month from the LocalDate object.

Integer week(LocalDate date)

Extracts the week from the LocalDate object.

Integer dayOfWeek(LocalDate date)

Extracts the day of week from the LocalDate object.

Integer dayOfMonth(LocalDate date)

Extracts the day of month from the LocalDate object.

Integer dayOfYear(LocalDate date)

Extracts the day of year from the LocalDate object.

Integer hourOfDay(LocalTime time)

Extracts the hour of the day from the LocalTime object.

Integer hourOfDay(DateTime dt)

Extracts the hour of the day from the DateTime object.

Integer minuteOfHour(LocalTime date)

Extracts the minute of hour from the LocalTime object.

Integer minuteOfHour(DateTime date)

Extracts the minute of hour from the DateTime object.

Integer minuteOfDay(DateTime date)

Extracts the minute of the day from the DateTime object.

Integer secondOfMinute(LocalTime date)

Extracts the second of minute from the LocalTime object.

Integer secondOfMinute(DateTime date)

Extracts the second of minute from the DateTime object.

Integer secondOfDay(DateTime date)

Extracts the second of day from the DateTime object.

Integer secondsBetween(LocalTime t1, LocalTime t2)

Returns the number of seconds between the two specified LocalTime instances. Example:

//returns number of seconds between 00:30:32 and time value in column 1.
secondsBetween(time('00:30:32'), time(value(1))

Integer secondsBetween(DateTime dt1, DateTime dt2)

Returns the number of seconds between the two specified DateTime instances.

Integer minutesBetween(LocalTime t1, LocalTime t2)

Returns the number of minutes between the two specified LocalTime instances.

Integer minutesBetween(DateTime dt1, DateTime dt2)

Returns the number of minutes between the two specified DateTime instances.

Integer hoursBetween(LocalTime t1, LocalTime t2)

Returns the number of hours between the two specified LocalTime instances.

Integer hoursBetween(DateTime dt1, DateTime dt2)

Returns the number of hours between the two specified DateTime instances.

Integer daysBetween(LocalDate d1, LocalDate d2)

Returns the number of days between the two specified LocalDate instances. Example:

//returns number of days between 2011-01-01 and value in column 1.
daysBetween(date('2011-01-01'), date(value(1))
//returns number of days between the actual date and value in current column 
daysBetween(date(actualDate()), date(value())

Integer daysBetween(DateTime dt1, DateTime dt2)

Returns the number of days between the two specified DateTime instances.

Integer weeksBetween(LocalDate d1, LocalDate d2)

Returns the number of weeks between the two specified LocalDate instances.

Integer weeksBetween(DateTime dt1, DateTime dt2)

Returns the number of weeks between the two specified DateTime instances.

Integer monthsBetween(LocalDate d1, LocalDate d2)

Returns the number of months between the two specified LocalDate instances.

Integer monthsBetween(DateTime dt1, DateTime dt2)

Returns the number of months between the two specified DateTime instances.

Integer yearsBetween(LocalDate d1, LocalDate d2)

Returns the number of years between the two specified LocalDate instances.

Integer yearsBetween(DateTime dt1, DateTime dt2)

Returns the number of years between the two specified DateTime instances.

Date time manipulation

DateTime, LocalDate and LocalTime objects are supported

Function

Desctipyion

DateTime plus(DateTime dt, String part, int count)

Formatting date time output

Function

Desctipyion

String toString(DateTime dt, String format)

Outputs the DateTime object in passed format

String toString(LocalDate date, String format)

Outputs the LocalDate object in passed format

String toString(LocalTime time, String format)

Outputs the LocalTime object in passed format

Date and Time Patterns

Date and time formats are specified by date and time pattern strings. Within date and time pattern strings, unquoted letters from 'A' to 'Z' and from 'a' to 'z' are interpreted as pattern letters representing the components of a date or time string. Text can be quoted using single quotes (') to avoid interpretation. "''" represents a single quote. All other characters are not interpreted; they're simply copied into the output string during formatting or matched against the input string during parsing.

The following pattern letters are defined (all other characters from 'A' to 'Z' and from 'a' to 'z' are reserved):

Letter

Date or Time Component

Examples

G

Era designator

AD

y

Year

1996; 96

M

Month in year

July; Jul; 07

w

Week in year

27

W

Week in month

2

D

Day in year

189

d

Day in month

10

F

Day of week in month

2

E

Day in week

Tuesday; Tue

a

Am/pm marker

PM

H

Hour in day (0-23)

0

k

Hour in day (1-24)

4

K

Hour in am/pm (0-11)

0

h

Hour in am/pm (1-12)

12

m

Minute in hour

30

s

Second in minute

55

S

Millisecond

978

z

Time zone

Pacific Standard Time; PST; GMT-08:00

Z

Time zone RFC 822

-0800

Another way how to compare two dates

Consider following example:
We have an import, which contains two date columns and we want the day difference of these two dates. Both date columns contains date in format yyyy/MM/dd.

def a1 = split(value(1), '/');
def date1 = [a1[0] as int, a1[1] as int, a1[2] as int] as Date;
def a2 = split(value(2), '/');
def date2 = [a2[0] as int, a2[1] as int, a2[2] as int] as Date;
return date2 - date1;
  • No labels