Date and Time Functions

The following date related functions are available in ODAS. 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