Alma Analytics
Date-Time Functions in a Nutshell

Author: Hisham Makki, Assistant Director for Data Quality.
Required knowledge: Basic Analytics.

Contents:

 

 
 

Introduction:

Analytics comes with many built-in functions that allow you to manipulate data. Date and time functions give you numerous options on how to modify, calculate, and extract date and time data. In this post, you will learn about Analytics date and time functions and how they work. This post is meant to be your quick reference guide to all datetime functions.  

Time Extraction Functions:

 

Function Note
SECOND (Time expr) Returns the number (between 0 and 59) corresponding to the seconds for a specified time expression.
MINUTE(Time expr) Returns the number (between 0 and 59) corresponding to the seconds for a specified time expression.
HOUR(Time expr) Returns a number (between 0 and 23) corresponding to the hour for a specified time expression. For example, 0 corresponds to 12 a.m. and 23 corresponds to 11 p.m.
CURRENT_TIME(Time expr) Returns the current time. The time is determined by the system in which the Oracle Analytics Server is running.

Example: Extract the hour from the current time

HOUR (CURRENT_TIME) 

 

Date Extraction Functions:

Day

Function Note
DAY (Time expr) Returns the number corresponding to the day of the month for a specified date expression. DayOfMonth also works.
DayOfWeek(Time expr) Returns a number between 1 and 7 corresponding to the day of the week for a specified date expression. For example, 1 always corresponds to Sunday, 2 corresponds to Monday, and so on through to Saturday which returns 7.
DAYNNAME(Time expr) Returns the name of the day of the week for a specified date expression.
DayOfYear(Time expr) Returns the number (between 1 and 366) corresponding to the day of the year for a specified date expression. Day_of_Year() also works.
DAY_OF_QUARTER(Time expr) Returns a number (between 1 and 92) corresponding to the day of the quarter for the specified date expression.
CURRENT_DATE Returns the current date. The date is determined by the system in which the Oracle Analytics Server is running.

Daytime:

Function Note
NOW() Returns the current date/timestamp. The timestamp is determined by the system in which the Oracle Analytics is running. Time expr is any integer representing the number of digits of precision with which to display the fractional second. The argument is optional; the function returns the default precision when no argument is specified. CURRENT_TIMESTAMP also works.
Week:
Function Note
WEEK_OF_QUARTER (Time expr) Returns a number (between 1 and 13) corresponding to the week of the quarter for the specified date expression.
WEEK_OF_YEAR(Time expr) Returns a number (between 1 and 53) corresponding to the week of the year for the specified date expression.
Month:
Function Note
MONTH (Time expr) Returns the number (between 1 and 12) corresponding to the month for a specified date expression.
MONTHNAME(Time expr) Returns the name of the month for a specified date expression.
MONTH_Of_QUARTER(Time expr) Returns the number (between 1 and 3) corresponding to the month in the quarter for a specified date expression.
Quarter:
Function Note
QUARTER (Time expr) Returns the number (between 1 and 4) corresponding to the quarter of the year for a specified date expression.
Year:
Function Note
YEAR (Time expr) Returns the year for the specified date expression.

Calculate Date-Time Functions:

You can use datetime functions to calculate datetime values for columns, filters, or dashboard prompts. There are two functions allow you to calculate datetime values:

  • TIMESTAMPDIFF function: Returns the total number of specified intervals between two timestamps.
    Syntax: TIMESTAMPDIFF (interval, timestamp1, timestamp2)

Valid interval values are:

SQL_TSI_FRAC_SECOND
SQL_TSI_SECOND
SQL_TSI_MINUTE
SQL_TSI_HOUR
SQL_TSI_DAY
SQL_TSI_WEEK
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_YEAR

Example: count number of days from creation date to current date

TIMESTAMPDIFF(SQL_TSI_DAY,  "Item Creation Date"."Item Creation Date", CURRENT_DATE)

 

  • TIMESTAMPADD function: Returns the timestamp calculated by adding, or subtracting, intervals.
    Syntax: TIMESTAMPADD (interval, count as integer, timestamp)

Example: add one week to the current date

TIMESTAMPADD(SQL_TSI_WEEK, 1, CURRENT_DATE)

Example: return a date that is one week earlier than today

TIMESTAMPADD(SQL_TSI_WEEK, -1, CURRENT_DATE)

Click here for more useful examples.

 

  Add Time Functions to Prompts

Sometimes you need to dynamically set a default date value in your date prompt. For example, you may need to set a prompt value to one week earlier to current date or set a default date to a month earlier to the most recent loan date in your database. 

  • In the Edit Prompt windows, change default selection to SQL Results.

  • In the SQL statement box, type a complete and simple SQL statement. Complete means the statement must begin with "SELECT", ends with "FROM {subject area)}. You may use "WHERE" condition to limit results. The following statement sets the default "Loan Date" value to a month earlier to the current date.

 

Example: Set default date value to a month earlier to the latest loan date saved in your database.

SELECT TIMESTAMPADD (SQL_TSI_MONTH, -1, MAX("Loan Date"."Loan Date")) FROM "Fulfillment"

 

Add Time Functions to Filters

 

To add a time function to a filter,

  • Convert the filter to SQL.

  • You only need to add a qualified SQL condition. No need to add a complete SQL statement.

Helpful Examples

 

These examples are collected from various online sources and tested on Alma Analytics

 

Example

First Day of the Previous Year

TIMESTAMPADD( SQL_TSI_YEAR , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 

First Day of the Current Year 

TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)

First Day of the Next Year

TIMESTAMPADD( SQL_TSI_YEAR , 1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

First Day of the Previous Month

TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

First Day of the Current Month

TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)

First Day of the Next Month

TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Last Day of the Previous Month

TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Last Day of Current Month

TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

Last Day of the Next Month

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 2, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

Last Day of Previous Year

TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1,  CURRENT_DATE))

Last Day of Current Year

TIMESTAMPADD(SQL_TSI_YEAR, 1, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

Last Day of the Next Year

TIMESTAMPADD(SQL_TSI_YEAR, 2, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

First Day of Current Quarter

TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)

Last Day of Current Quarter

TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_QUARTER , 1, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

Number of days between First Day of Year 2010 and Last Day of Current Month

TIMESTAMPDIFF(SQL_TSI_DAY, date '2020-01-01', TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))))

Fiscal Year (tested on Primo VE Analytics)

CASE
    WHEN EXTRACT(MONTH FROM "Dates"."Date") BETWEEN 7 AND 12 THEN EXTRACT(YEAR FROM "Dates"."Date") +1
    WHEN EXTRACT(MONTH FROM "Dates"."Date") BETWEEN 1 AND 6 THEN EXTRACT(YEAR FROM "Dates"."Date")
       ELSE NULL    
END

First Date of Current Fiscal Year 

SELECT 
      CAST(
           TRIM(CAST(YEAR(CURRENT_DATE) AS CHAR)
           )||'/07/01' AS DATE)
FROM "Funds Expenditure"