Date and Time Functions¶

The following date related functions are available in Okera. The commonly operate on the date data types, that is, TIMESTAMP and DATE. You can use TIMESTAMP where DATE is needed, in which case the time part of the value is ignored.

Function	Description
`add_months()`	Adds a given amount of months to a given timestamp
`adddate()`	Adds a specified number of days to a timestamp
`current_date()`	Returns the current date as a `DATE` value
`current_timestamp()`	Returns the current date as a `TIMESTAMP` value, same as `now()`
`date_add()`	Similar to `adddate()` but also supports intervals
`date_part()`	Extracts a part of a given timestamp, similar to `extract()`
`date_sub()`	Similar to `date_add()` but substracts instead
`datediff()`	Returns number of days between two specified timestamps
`day()`	Returns the day field from the date part of a specified timestamp
`dayname()`	Returns the day field from a given timestamp as a readable string
`dayofmonth()`	Same as `day()` function
`dayofweek()`	Returns the day of the week (1 to 7) for a given timestamp
`dayofyear()`	Same as above, but for the day in the year (1 to 366)
`days_add()`	Same as `date_add()` function
`days_sub()`	Same as `date_sub()` function
`extract()`	Returns a field from the date or time of a timestamp
`from_timestamp()`	Converts a timestamp into a string, applying a pattern
`from_unixtime()`	Converts an epoch-based numeric date into a string
`from_utc_timestamp()`	Translates UTC based timestamps into time zone based ones
`hour()`	Returns the hour field of a timestamp
`hours_add()`	Adds a given number of hours to a specified timestamp
`hours_sub()`	Same as above, but substracts hours instead
`int_months_between()`	Returns the number of months between two timestamps
`microseconds_add()`	Adds a given number of microseconds to a specified timestamp
`microseconds_sub()`	Same as above, but substracts microseconds instead
`millisecond()`	Returns the millisecond part of a timestamp
`milliseconds_add()`	Adds a given number of milliseconds to a specified timestamp
`milliseconds_sub()`	Same as above, but substracts milliseconds instead
`minute()`	Returns the minute field of a specified timestamp
`minutes_add()`	Adds a given number of minutes to a specified timestamp
`minutes_sub()`	Same as above, but substracts minutes instead
`month()`	Returns the month field of a specified timestamp
`months_add()`	Adds a given number of months to a specified timestamp
`months_between()`	Similar to `int_months_beteen()` but includes fractional parts
`months_sub()`	Same as above, but substracts months instead
`nanoseconds_add()`	Adds a given number of nanoseconds to a specified timestamp
`nanoseconds_sub()`	Same as above, but substracts nanoseconds instead
`now()`	Returns the current date and time
`second()`	Returns the seconds field of a specified timestamp
`seconds_add()`	Adds a given number of seconds to a specified timestamp
`seconds_sub()`	Same as above, but substracts seconds instead
`subdate()`	Same as `adddate()` but substracts instead
`timeofday()`	Returns the current time and date with a specific formatting
`timestamp_cmp()`	Compares two timestamps and returns a comparison result (similar to `sign()`)
`to_date()`	Converts the date part of a timestamp into a string
`to_timestamp()`	Converts a numeric (epoch) or string value into a timestamp
`to_unix_timestamp()`	Same as `to_timestamp()` but returns a numeric value
`to_utc_timestamp()`	Same as `to_timestamp()` but also applies time zones
`trunc()`	Removes fields from a specified timestamp
`unix_timestamp()`	Returns the current date as an epoch-based number
`weekofyear()`	Returns the current week of the year number
`weeks_add()`	Adds a given number of weeks to a specified timestamp
`weeks_sub()`	Same as above, but substracts weeks instead
`year()`	Returns the year field of a specified timestamp
`years_add()`	Adds a given number of years to a specified timestamp
`years_sub()`	Same as above, but substracts years instead

The following explains some of the functions and statements in more detail.

TIMESTAMP date_add(TIMESTAMP, INT <days>) and TIMESTAMP date_add(TIMESTAMP, <interval_expression>)

Adds a specified amount of days or an interval to a given time stamp. The function corrects dates as necessary (see last example).

Example: Using the date_add() function

> SELECT now() AS right_now, date_add(now(), interval 3 weeks) AS in_3_weeks;
right_now                            in_3_weeks
2019-10-04 08:50:10.810000+00:00     2019-10-25 08:50:10.810000+00:00

> SELECT date_add(cast('2019-01-31' AS timestamp), interval 3 months); -- overrun
2019-04-30 00:00:00+00:00

TIMESTAMP trunc(TIMESTAMP, STRING)

Removes fields from a given timestamp. This includes date operations to round dates to, for example, start of a quarter. The unit to remove is specified as a STRING, with the following possible values:

Unit	Pattern
Year	`SYYYY`, `YYYY`, `YEAR`, `SYEAR`, `YYY`, `YY`, `Y`
Month	`MONTH`, `MON`, `MM`, `RM`
Day	`DDD`, `DD`, `J`
Hour	`HH`, `HH12`, `HH24`
Minute	`MI`
Day of Week	`DAY`, `DY`, `D`
Same Day of Week as First Day of Year/Month	`WW`, `W`
Quarter	`Q`

Example: Using the trunc() function

> SELECT now() AS right_now, trunc(now(), 'Q') AS current_quarter;
right_now                             current_quarter
2019-10-04 09:56:50.344000+00:00      2019-10-01 00:00:00+00:00

> SELECT trunc(cast('2019-09-12' AS TIMESTAMP), 'WW');
2019-09-08 00:00:00+00:00
> SELECT dayofweek(cast('2019-01-01' AS TIMESTAMP));
3
> SELECT dayofweek(trunc(cast('2019-10-12' AS TIMESTAMP), 'WW'));
3

> SELECT trunc(cast('2019-09-12' AS TIMESTAMP), 'WW');
2019-09-10 00:00:00+00:00
> SELECT dayofweek(trunc(cast('2019-09-12' AS TIMESTAMP), 'WW'));
3
> SELECT dayofweek(cast('2019-01-01' AS TIMESTAMP));
3

> SELECT trunc(cast('2019-09-12' AS TIMESTAMP), 'W');
2019-09-08 00:00:00+00:00
> SELECT dayofweek(trunc(cast('2019-09-12' AS TIMESTAMP), 'W'));
1
> SELECT dayofweek(cast('2019-09-01' AS TIMESTAMP));
1