Date

GeaFlow support the following date function:

• from_unixtime
• from_unixtime_millis
• unix_timestamp
• unix_timestamp_millis
• isdate
• now
• day
• weekday
• lastday
• day_of_month
• week_of_year
• date_add
• date_sub
• date_diff
• add_months
• date_format
• date_part
• date_trunc

from_unixtime

Syntax

string from_unixtime(int unixtime)
string from_unixtime(long unixtime)
string from_unixtime(int unixtime, string format)
string from_unixtime(long unixtime, string format)
string from_unixtime(string unixtime, string format)

Description Translate unix timestamp to the format time. The default format is "yyyy-MM-dd HH:mm:ss". Return Null if any of the input is null.

Example

from_unixtime(11111111) = '1970-05-09 22:25:11'
from_unixtime(11111111, 'yyyy-MM-dd HH:mm:ss.SSSSSS') = '1970-05-09 22:25:11.000000'

from_unixtime_millis

Syntax

string from_unixtime_millis(int unixtime)
string from_unixtime_millis(long unixtime)
string from_unixtime_millis(string unixtime)
string from_unixtime_millis(int unixtime, string format)
string from_unixtime_millis(long unixtime, string format)

Description Translate unix millis timestamp to the format time. The default format is "yyyy-MM-dd HH:mm:ss.SSS". Return Null if any of the input is null. Example

from_unixtime_millis(11111111) = '1970-01-01 11:05:11.111'
from_unixtime_millis(11111111, 'yyyy-MM-dd HH:mm:ss') = '1970-01-01 11:05:11'
from_unixtime_millis(11111111, 'yyyy-MM-dd HH:mm:ss.SSSSSS') = '1970-01-01 11:05:11.111000'

unix_timestamp

Syntax

long unix_timestamp()
long unix_timestamp(string dateText)
long unix_timestamp(string dateText, string patternText)

Description Return Unix timestamp. If no argument is provided, return the current timestamp. If dateText is given, return the corresponding timestamp. When the format patternText is not specified, parse dateText using the default formats "yyyy-MM-dd", "yyyy-MM-dd HH:mm:ss" or "yyyy-MM-dd HH:mm:ss.SSSSSS". Return Null if any of the input is null.

Example

unix_timestamp('1987-06-05 00:11:22') = 549817882
unix_timestamp('1987-06-05 00:11', 'yyyy-MM-dd HH:mm') = 549817860

unix_timestamp_millis

Syntax

long unix_timestamp_millis()
long unix_timestamp_millis(string dateText)
long unix_timestamp_millis(string dateText, string patternText)

Description Return Unix millis timestamp. Similar to the function unix_timestamp. Example

unix_timestamp_millis('1987-06-05 00:11:22') = 549817882000
unix_timestamp_millis('1987-06-05', 'yyyy-mm-dd') = 536774760000

isdate

Syntax

boolean isdate(string date)
boolean isdate(string date, string format)

Description Returns whether string is a date of the format. The default format is "yyyy-MM-dd HH:mm:ss". Return false if any of the input is null.

Example

isdate('1987-06-05 00:11:22') = true
isdate('xxxxxxxxxxxxx') = false
isdate('1987-06-05 00:11:22', 'yyyy-MM-dd HH:mm:ss.SSSSSS') = false

now

Syntax

long now()
long now(int offset)
long now(long offset)

Description Returns the current timestamp with an optional offset.

Example

now()
now(1000)

day

Syntax

int day(string dateString)

Description Returns the day of date. The default format is "yyyy-MM-dd" or "yyyy-MM-dd HH:mm:ss". Return null if any of the input is null.

Example

day('1987-06-05 00:11:22') = 5

weekday

Syntax

int weekday(string dateString)

Description Returns the weekday of date. The default format is "yyyy-MM-dd" or "yyyy-MM-dd HH:mm:ss". Return null if any of the input is null.

Example

weekday('1987-06-05 00:11:22') = 5

lastday

Syntax

string lastday(string dateString)

Description Returns the last day of the month which the date belongs to. The default format is "yyyy-MM-dd" or "yyyy-MM-dd HH:mm:ss". Return null if any of the input is null.

Example

lastday('1987-06-05') = '1987-06-30 00:00:00'

day_of_month

Syntax

int day_of_month(string dateString)

Description Returns the date of the month of date. The default format is "yyyy-MM-dd" or "yyyy-MM-dd HH:mm:ss". Return null if any of the input is null.

Example

day_of_month('1987-06-05 00:11:22') = 5

week_of_year

Syntax

int week_of_year(string dateString)

Description Returns the week of the year of the given date. The default format is "yyyy-MM-dd" or "yyyy-MM-dd HH:mm:ss". Return null if any of the input is null.

Example

week_of_year('1987-06-05 00:11:22') = 23

date_add

Syntax

string date_add(string date, int days)

Description Add a number of days to the specified date. The default format is "yyyy-MM-dd" or "yyyy-MM-dd HH:mm:ss". Return Null if any of the input is null.

Example

date_add('2017-09-25 10:00:00', 1) = '2017-09-26'
date_add('2017-09-25', 1) = '2017-09-26'
date_add('2017-09-25', -1) = '2017-09-24'

date_sub

Syntax

string date_sub(string date, int days)

Description Sub a number of days to the specified date. The default format is "yyyy-MM-dd" or "yyyy-MM-dd HH:mm:ss". Return Null if any of the input is null.

Example

date_sub('2017-09-25 10:00:00', 1) = '2017-09-24'
date_sub('2017-09-25', 1) = '2017-09-24'
date_sub('2017-09-25', -1) = '2017-09-26'

date_diff

Syntax

int date_diff(string dateString1, string dateString2)

Description Returns the number of days from dateString2 to dateString1. The default format is "yyyy-MM-dd" or "yyyy-MM-dd HH:mm:ss". Return Null if any of the input is null.

Example

date_diff('2017-09-26', '2017-09-25') = 1
date_diff('2017-09-24', '2017-09-25') = -1

add_months

Syntax

string add_months(string date, int months)

Description Add a number of months to the specified date. The default format is "yyyy-MM-dd" or "yyyy-MM-dd HH:mm:ss". Return Null if any of the input is null.

Example

add_months('2017-09-25 10:00:00', 1) = '2017-10-25 10:00:00'
add_months('2017-09-25', 1) = '2017-10-25'
add_months('2017-09-25', -1) = '2017-08-25'

date_format

Syntax

string date_format(string dateText)
string date_format(string dateText, string toFormat)
string date_format(string dateText, string fromFormat, string toFormat)

Description Returns convert the date from a format to another. The default fromFormat is "yyyy-MM-dd HH:mm:ss" or "yyyy-MM-dd HH:mm:ss.SSSSSS", and the default toFormat is "yyyy-MM-dd HH:mm:ss". Return Null if any of the input is null.

Example

date_format('1987-06-05 00:11:22') = '1987-06-05 00:11:22'
date_format('1987-06-05 00:11:22', 'MM-dd-yyyy') = '06-05-1987'
date_format('00:11:22 1987-06-05', 'HH:mm:ss yyyy-MM-dd', 'MM-dd-yyyy') = '06-05-1987'

date_part

Syntax

int date_part(string dateText, string datePart)

Description Returns part of the date by date part format. The default date format is "yyyy-MM-dd HH:mm:ss" or "yyyy-MM-dd". Return Null if any of the input is null. The datePart format is as shown in the table below.

Select	datePart
YEAR	yyyy, year
MONTH	mm, mon, month
DAY_OF_MONTH	dd, day
HOUR_OF_DAY	hh, hour
MINUTE	mi, minute
SECOND	ss, second

Example

date_part('1987-06-05 00:11:22', 'yyyy') = 1987
date_part('1987-06-05 00:11:22', 'mm') = 6
date_part('1987-06-05 00:11:22', 'dd') = 5
date_part('1987-06-05 00:11:22', 'hh') = 0
date_part('1987-06-05 00:11:22', 'mi') = 11
date_part('1987-06-05 00:11:22', 'ss') = 22
date_part('1987-06-05', 'ss') = 0

date_trunc

Syntax

string date_trunc(string dateText, string datePart)

Description Returns date truncated to the unit specified by the format. The default date format is "yyyy-MM-dd HH:mm:ss" or "yyyy-MM-dd". Return Null if any of the input is null. The datePart format is the same as the function date_part.

Example

date_trunc('1987-06-05 00:11:22', 'yyyy') = '1987-01-01 00:00:00'
date_trunc('1987-06-05 00:11:22', 'mm') = '1987-06-01 00:00:00'
date_trunc('1987-06-05 00:11:22', 'dd') = '1987-06-05 00:00:00'
date_trunc('1987-06-05 00:11:22', 'hh') = '1987-06-05 00:00:00'
date_trunc('1987-06-05 00:11:22', 'mi') = '1987-06-05 00:11:00'
date_trunc('1987-06-05 00:11:22', 'ss') = '1987-06-05 00:11:22'
date_trunc('1987-06-05', 'ss') = '1987-01-01 00:00:00'