Using Timestamp functions in queries

You can perform various arithmetic operations on Timestamp and Duration values.

If you want to follow along with the examples, see to view a sample data and use the scripts to load sample data for testing. The scripts create the tables used in the examples and load data into the tables.

If you want to follow along with the examples, see Sample data to run queries to view a sample data and learn how to use OCI console to create the example tables and load data using JSON files.

Related Topics

Extract Expressions

The EXTRACT expression extracts a component from a timestamp.
extract_expression ::= EXTRACT "(" id FROM expression ")"

The expression after the FROM keyword must return at most one timestamp or NULL. If the result of this expression is NULL or empty, the result of EXTRACT is also NULL or empty, respectively. Otherwise, the component specified by the id is returned. This id must be one of the following keywords: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, NANOSECOND, WEEK, ISOWEEK.

Example 1: What is the full name and baggage arrival year for the customer with ticket number 1762383911861.
SELECT fullName, 
EXTRACT (YEAR FROM CAST (bag.bagInfo.bagArrivalDate AS Timestamp(0)))
AS YEAR FROM BaggageInfo bag 
WHERE ticketNo=1762383911861

Explanation: You first use CAST to convert the bagArrivalDate to a TIMESTAMP and then fetch the YEAR component from the Timestamp.

Output: 
{"fullName":"Joanne Diaz","YEAR":2019}
Example 2: Retrieve all bags that travelled through MIA between 10:00 am and 10:00 pm in February 2019.
SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc,
$t1 AS HOUR FROM BaggageInfo bag,
EXTRACT(HOUR FROM CAST (bag.bagInfo[0].bagArrivalDate AS Timestamp(0))) $t1,
EXTRACT(YEAR FROM CAST (bag.bagInfo[0].bagArrivalDate AS Timestamp(0))) $t2,
EXTRACT(MONTH FROM CAST (bag.bagInfo[0].bagArrivalDate AS Timestamp(0))) $t3
WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any "MIA" AND
$t2=2019 AND $t3=02 AND ($t1>10 AND $t1<20)

Explanation: You want to know the details of flights that traveled through MIA between 10:00 am and 10:00 pm in February 2019. You use a number of filter conditions here. First, the flight should have originated or traversed through MIA. The year of arrival should be 2019 and the month of arrival should be 2 (February). Then you filter if the hour of arrival is between 10:00 am and 10:00 pm (20 hours).

Output: 
{"tagNum":"17657806255240","fltRouteSrc":["MIA","LAX"],"HOUR":16}
{"tagNum":"17657806292518","fltRouteSrc":["MIA","LAX"],"HOUR":16}
Example 3: Which year and month did the passenger with the reservation code PQ1M8N receive the baggage?
SELECT fullName, 
EXTRACT(YEAR FROM CAST (bag.bagInfo.bagArrivalDate AS Timestamp(0))) AS YEAR, 
EXTRACT(MONTH FROM CAST (bag.bagInfo.bagArrivalDate AS Timestamp(0))) AS MONTH 
FROM BaggageInfo bag WHERE bag.confNo="PQ1M8N"

Explanation: You first use CAST to convert the bagArrivalDate to a TIMESTAMP and then fetch the YEAR component and MONTH component from the Timestamp.

Output:
{"fullName":"Kendal Biddle","YEAR":2019,"MONTH":3}
Example 4: Group the baggage data based on the month of arrival and display the month and the number of baggage that arrived that month.
SELECT EXTRACT(MONTH FROM CAST ($bag_arr_date AS Timestamp(0))) AS MONTH,
count(EXTRACT(MONTH FROM CAST ($bag_arr_date AS Timestamp(0)))) AS COUNT
FROM BaggageInfo $bag, $bag.bagInfo[].bagArrivalDate $bag_arr_date 
GROUP BY EXTRACT(MONTH FROM CAST ($bag_arr_date AS Timestamp(0)))
Explanation: You want to group the data based on the month of the arrival of baggage. You use an unnest array to flatten the data. The bagInfo array is flattened and the value of bag arrival date is fetched from the array. You then use CAST to convert the bagArrivalDate to a TIMESTAMP and then fetch the YEAR component and MONTH component from the Timestamp. You then use the count function to get the total baggage corresponding to every month.

Note:

One assumption in the data is that all the baggage has arrived in the same year. So you group the data only based on the month.
Output:
{"MONTH":2,"COUNT":11}
{"MONTH":3,"COUNT":10}

timestamp_add() function

Adds a duration to a timestamp value and returns the new timestamp. The duration can be positive or negative. The result type is TIMESTAMP(9).

Syntax:
TIMESTAMP(9) timestamp_add(TIMESTAMP timestamp, STRING duration)
Semantics:
  • timestamp: A TIMESTAMP value or a value that can be cast to TIMESTAMP.
  • duration: A string with format [-](<n> <UNIT>)+, where 'n' is a number and the <UNIT> can be YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, NANOSECOND or the plural form of these keywords (e.g. YEARS).

    Note:

    The UNIT keyword is case-insensitive.
  • returnvalue: TIMESTAMP(9)
Example 1: In the airline application, a buffer of five minutes delay is considered "on time" . Print the estimated arrival time on the first leg with a buffer of five minutes for the passenger with ticket number 1762399766476. 
SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")
AS ARRIVAL_TIME FROM BaggageInfo bag 
WHERE ticketNo=1762399766476

Explanation : In the airline application, a customer can have any number of flight legs depending on the source and destination. In the query above, you are fetching the estimated arrival in the "first leg" of the travel. So the first record of the flightsLeg array is fetched and the estimatedArrival time is fetched from the array and a buffer of "5 minutes" is added to that and displayed.

Output: 
{"ARRIVAL_TIME":"2019-02-03T06:05:00.000000000Z"}

Note:

The column estimatedArrival is a STRING. If the column has STRING values in ISO-8601 format, then it will be automatically converted by the SQL runtime into TIMESTAMP data type.

ISO8601 describes an internationally accepted way to represent dates, times, and durations.

Syntax: Date with time: YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]][Z|(+|-)hh:mm]

where
  • YYYY specifies the year, as four decimal digits
  • MM specifies the month, as two decimal digits, 00 to 12
  • DD specifies the day, as two decimal digits, 00 to 31
  • hh specifies the hour, as two decimal digits, 00 to 23
  • mm specifies the minutes, as two decimal digits, 00 to 59
  • ss[.s[s[s[s[s]]]]] specifies the seconds, as two decimal digits, 00 to 59, optionally followed by a decimal point and 1 to 6 decimal digits (representing the fractional part of a second).
  • Z specifies UTC time (time zone 0). (It can also be specified by +00:00, but not by –00:00.)
  • (+|-)hh:mm specifies the time-zone as difference from UTC. (One of + or – is required.)
Example 1a: Print the estimated arrival time in every leg with a buffer of five minutes for the passenger with ticket number 1762399766476.
SELECT $s.ticketno, $value as estimate, 
timestamp_add($value, '5 minute') AS add5min
FROM baggageinfo $s,
$s.bagInfo.flightLegs.estimatedArrival as $value
WHERE ticketNo=1762399766476

Explanation: You want to display the estimatedArrival time on every leg. The number of legs can be different for every customer. So variable reference is used in the query above and the baggageInfo array and the flightLegs array are unnested to execute the query.

Output:
{"ticketno":1762399766476,"estimate":"2019-02-03T06:00:00Z",
"add5min":"2019-02-03T06:05:00.000000000Z"}
{"ticketno":1762399766476,"estimate":"2019-02-03T08:22:00Z",
"add5min":"2019-02-03T08:27:00.000000000Z"}
Example 2 : How many bags arrived in the last week?
SELECT count(*) AS COUNT_LASTWEEK FROM baggageInfo bag 
WHERE EXISTS bag.bagInfo[$element.bagArrivalDate < current_time()
AND $element.bagArrivalDate > timestamp_add(current_time(), "-7 days")]

Explanation: You get a count of the number of bags processed by the airline application in the last week. A customer can have more than one bag( that is bagInfo array can have more than one record). ThebagArrivalDate should have a value between today and the last 7 days. For every record in the bagInfo array, you determine if the bag arrival time is between the time now and one week ago. The function current_time gives you the time now. An EXISTS condition is used as a filter for determining if the bag has an arrival date in the last week. The count function determines the total number of bags in this time period.

Output: 
{"COUNT_LASTWEEK":0}
Example 3: Find the number of bags arriving in the next 6 hours.
SELECT count(*) AS COUNT_NEXT6HOURS FROM baggageInfo bag 
WHERE EXISTS bag.bagInfo[$element.bagArrivalDate > current_time()
AND $element.bagArrivalDate < timestamp_add(current_time(), "6 hours")]

Explanation: You get a count of the number of bags that will be processed by the airline application in the next 6 hours. A customer can have more than one bag( that isbagInfo array can have more than one record). The bagArrivalDate should be between the time now and the next 6 hours. For every record in the bagInfo array, you determine if the bag arrival time is between the time now and six hours later. The function current_time gives you the time now. An EXISTS condition is used as a filter for determining if the bag has an arrival date in the next six hours. The count function determines the total number of bags in this time period.

Output: 
{"COUNT_NEXT6HOURS":0}

timestamp_diff() and get_duration() functions

timestamp_diff()

Returns the number of milliseconds between two timestamp values. The result type is LONG.

Syntax:
LONG timestamp_diff(TIMESTAMP timestamp1, TIMESTAMP
      timestamp2)
Semantics:
  • timestamp1: A TIMESTAMP value or a value that can be cast to TIMESTAMP
  • timestamp2: A TIMESTAMP value or a value that can be cast to TIMESTAMP
  • returnvalue: LONG

get_duration()

Converts the given number of milliseconds to a duration string. The result type is STRING.

Syntax:
STRING get_duration(LONG duration_millis)
Semantics:
  • duration_millis: the duration in milliseconds
  • returnvalue: STRING. The returned duration string format is [-](<n> <UNIT>)+, where the <UNIT> can be DAY, HOUR, MINUTE, SECOND and MILLISECOND, e.g. "1 day 2 hours" or "-10 minutes 0 second 500 milliseconds".

Examples:

Example 1: What is the duration between the time the baggage was boarded at one leg and reached the next leg for the passenger with ticket number 1762355527825? 
SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff
FROM baggageinfo $s, 
$s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg
WHERE ticketNo=1762355527825

Explanation: In an airline application every customer can have a different number of hops/legs between their source and destination. In this query, you determine the time taken between every flight leg. This is determined by the difference between bagArrivalDate and flightDate for every flight leg. To determine the duration in days or hours or minutes, pass the result of the timestamp_diff function to the get_duration function.

Output: 
{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T07:00:00Z",
"diff":"3 hours 17 minutes"}
{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T07:23:00Z",
"diff":"2 hours 54 minutes"}
{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T08:23:00Z",
"diff":"1 hour 54 minutes"}
To determine the duration in milliseconds, use only the timestamp_diff function.
SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate) AS diff
FROM baggageinfo $s, 
$s.bagInfo[] AS $bagInfo, 
$bagInfo.flightLegs[] AS $flightLeg
WHERE ticketNo=1762355527825
Example 2: How long does it take from the time of check-in to the time the bag is scanned at the point of boarding for the passenger with ticket number 176234463813?
SELECT $flightLeg.flightNo,
$flightLeg.actions[contains($element.actionCode, "Checkin")].actionTime AS checkinTime,
$flightLeg.actions[contains($element.actionCode, "BagTag Scan")].actionTime AS bagScanTime,
get_duration(timestamp_diff(
    $flightLeg.actions[contains($element.actionCode, "Checkin")].actionTime,
    $flightLeg.actions[contains($element.actionCode, "BagTag Scan")].actionTime
)) AS diff
FROM baggageinfo $s, 
$s.bagInfo[].flightLegs[] AS $flightLeg
WHERE ticketNo=176234463813 AND 
starts_with($s.bagInfo[].routing, $flightLeg.fltRouteSrc)

Explanation: In the baggage data, every flightLeg has an actions array. There are three different actions in the action array. The action code for the first element in the array is Checkin/Offload. For the first leg, the action code is Checkin and for the other legs, the action code is Offload at the hop. The action code for the second element of the array is BagTag Scan. In the query above, you determine the difference in action time between the bag tag scan and check-in time. You use the contains function to filter the action time only if the action code is Checkin or BagScan. Since only the first flight leg has details of check-in and bag scan, you additionally filter the data using starts_with function to fetch only the source code fltRouteSrc. To determine the duration in days or hours or minutes, pass the result of the timestamp_diff function to the get_duration function.

To determine the duration in milliseconds, only use the timestamp_diff function.
SELECT $flightLeg.flightNo,
$flightLeg.actions[contains($element.actionCode, "Checkin")].actionTime AS checkinTime,
$flightLeg.actions[contains($element.actionCode, "BagTag Scan")].actionTime AS bagScanTime,
timestamp_diff(
   $flightLeg.actions[contains($element.actionCode, "Checkin")].actionTime,
   $flightLeg.actions[contains($element.actionCode, "BagTag Scan")].actionTime
) AS diff
FROM baggageinfo $s, 
$s.bagInfo[].flightLegs[] AS $flightLeg
WHERE ticketNo=176234463813 AND 
starts_with($s.bagInfo[].routing, $flightLeg.fltRouteSrc)
Output: 
{"flightNo":"BM572","checkinTime":"2019-03-02T03:28:00Z",
"bagScanTime":"2019-03-02T04:52:00Z","diff":"- 1 hour 24 minutes"}
Example 3: How long does it take for the bags of a customer with ticket no 1762320369957 to reach the first transit point? 
SELECT  $bagInfo.flightLegs[1].actions[2].actionTime,
$bagInfo.flightLegs[0].actions[0].actionTime,
get_duration(timestamp_diff($bagInfo.flightLegs[1].actions[2].actionTime,
                            $bagInfo.flightLegs[0].actions[0].actionTime)) AS diff
FROM baggageinfo $s, $s.bagInfo[] AS $bagInfo     
WHERE ticketNo=1762320369957

Explanation: In an airline application every customer can have a different number of hops/legs between their source and destination. In the example above, you determine the time taken for the bag to reach the first transit point. In the baggage data, the flightLeg is an array. The first record in the array refers to the first transit point details. The flightDate in the first record is the time when the bag leaves the source and the estimatedArrival in the first flight leg record indicates the time it reaches the first transit point. The difference between the two gives the time taken for the bag to reach the first transit point. To determine the duration in days or hours or minutes, pass the result of the timestamp_diff function to the get_duration function.

To determine the duration in milliseconds, use the timestamp_diff function. 
SELECT  $bagInfo.flightLegs[0].flightDate,
$bagInfo.flightLegs[0].estimatedArrival,
timestamp_diff($bagInfo.flightLegs[0].estimatedArrival,
             $bagInfo.flightLegs[0].flightDate) AS diff
FROM baggageinfo $s, $s.bagInfo[] AS $bagInfo
WHERE ticketNo=1762320369957
Output: 
{"flightDate":"2019-03-12T03:00:00Z","estimatedArrival":"2019-03-12T16:00:00Z","diff":"13 hours"}
{"flightDate":"2019-03-12T03:00:00Z","estimatedArrival":"2019-03-12T16:40:00Z","diff":"13 hours 40 minutes"}

Examples using QueryRequest API

You can use QueryRequest API and apply SQL functions to fetch data from a NoSQL table.

To execute your query, you use the NoSQLHandle.query() API.

Download the full code SQLFunctions.java from the examples here.
 //Fetch rows from the table
private static void fetchRows(NoSQLHandle handle,String sqlstmt) throws Exception {
   try (
      QueryRequest queryRequest = new QueryRequest().setStatement(sqlstmt);
      QueryIterableResult results = handle.queryIterable(queryRequest)){
      for (MapValue res : results) {
         System.out.println("\t" + res);
      }
   }
} 
String ts_func1="SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, \"5 minutes\")"+
                         " AS ARRIVAL_TIME FROM BaggageInfo bag WHERE ticketNo=1762341772625";
System.out.println("Using timestamp_add function ");
fetchRows(handle,ts_func1);
String ts_func2="SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate, "+
                "get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff "+
                 "FROM baggageinfo $s, $s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg "+
                 "WHERE ticketNo=1762344493810";
System.out.println("Using get_duration and timestamp_diff function ");
fetchRows(handle,ts_func2);

To execute your query use the borneo.NoSQLHandle.query() method.

Download the full code SQLFunctions.py from the examples here.
# Fetch data from the table
def fetch_data(handle,sqlstmt):
   request = QueryRequest().set_statement(sqlstmt)
   print('Query results for: ' + sqlstmt)
   result = handle.query(request)
   for r in result.get_results():
      print('\t' + str(r))  
 ts_func1 = '''SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")
                 AS ARRIVAL_TIME FROM BaggageInfo bag WHERE ticketNo=1762341772625'''
print('Using timestamp_add function:')
fetch_data(handle,ts_func1)

ts_func2 = '''SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
              get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff
              FROM baggageinfo $s,
              $s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg
              WHERE ticketNo=1762344493810'''
print('Using get_duration and timestamp_diff function:')
fetch_data(handle,ts_func2)

To execute a query use the Client.Query function.

Download the full code SQLFunctions.go from the examples here.
 //fetch data from the table
func fetchData(client *nosqldb.Client, err error, tableName string, querystmt string)(){
   prepReq := &nosqldb.PrepareRequest{
		Statement: querystmt,
   }
   prepRes, err := client.Prepare(prepReq)
   if err != nil {
      fmt.Printf("Prepare failed: %v\n", err)
      return
   }
   queryReq := &nosqldb.QueryRequest{
		 PreparedStatement: &prepRes.PreparedStatement,   }
   var results []*types.MapValue
   for {
      queryRes, err := client.Query(queryReq)
      if err != nil {
         fmt.Printf("Query failed: %v\n", err)
  	return
      }
      res, err := queryRes.GetResults()
      if err != nil {
         fmt.Printf("GetResults() failed: %v\n", err)
  	return
      }
      results = append(results, res...)
      if queryReq.IsDone() {
         break
      }
   }
   for i, r := range results {
      fmt.Printf("\t%d: %s\n", i+1, jsonutil.AsJSON(r.Map()))
   }
} 
ts_func1 := `SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")
                AS ARRIVAL_TIME FROM BaggageInfo bag WHERE ticketNo=1762341772625`
fmt.Printf("Using timestamp_add function::\n")
fetchData(client, err,tableName,ts_func1)

ts_func2 := `SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
             get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff
             FROM baggageinfo $s,
             $s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg
             WHERE ticketNo=1762344493810`
fmt.Printf("Using get_duration and timestamp_diff function:\n")
fetchData(client, err,tableName,ts_func2)

To execute a query use query method.

JavaScript: Download the full code SQLFunctions.js from the examples here.
  //fetches data from the table
async function fetchData(handle,querystmt) {
   const opt = {};
   try {
      do {
         const result = await handle.query(querystmt, opt);
         for(let row of result.rows) {
            console.log('  %O', row);
         }
         opt.continuationKey = result.continuationKey;
      } while(opt.continuationKey);
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}
TypeScript: Download the full code SQLFunctions.ts from the examples here.
interface StreamInt {
   acct_Id: Integer;
   profile_name: String;
   account_expiry: TIMESTAMP;
   acct_data: JSON;
}
/* fetches data from the table */
async function fetchData(handle: NoSQLClient,querystmt: string) {
   const opt = {};
   try {
      do {
         const result = await handle.query<StreamInt>(querystmt, opt);
         for(let row of result.rows) {
            console.log('  %O', row);
         }
         opt.continuationKey = result.continuationKey;
      } while(opt.continuationKey);
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}

const ts_func1 = `SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")
                  AS ARRIVAL_TIME FROM BaggageInfo bag WHERE ticketNo=1762341772625`
console.log("Using timestamp_add function:");
await fetchData(handle,ts_func1);

const ts_func2 = `SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
                  get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff
                  FROM baggageinfo $s,
                  $s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg
                  WHERE ticketNo=1762344493810`
console.log("Using get_duration and timestamp_diff function:");
await fetchData(handle,ts_func2);

To execute a query, you may call QueryAsync method or call GetQueryAsyncEnumerable method and iterate over the resulting async enumerable.

Download the full code SQLFunctions.cs from the examples here.
private static async Task fetchData(NoSQLClient client,String querystmt){
   var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
   await DoQuery(queryEnumerable);
}

private static async Task DoQuery(IAsyncEnumerable<QueryResult<RecordValue>> queryEnumerable){
   Console.WriteLine("  Query results:");
   await foreach (var result in queryEnumerable) {
      foreach (var row in result.Rows)
      {
         Console.WriteLine();
         Console.WriteLine(row.ToJsonString());
      }
   }
} 
private const string ts_func1 =@"SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, ""5 minutes"")
                                       AS ARRIVAL_TIME FROM BaggageInfo bag WHERE ticketNo=1762341772625";
Console.WriteLine("\nUsing timestamp_add function!");
await fetchData(client,ts_func1);

private const string ts_func2 =@"SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
                                       get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff
                                       FROM baggageinfo $s,
                                       $s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg
                                       WHERE ticketNo=1762344493810";
Console.WriteLine("\nUsing get_duration and timestamp_diff function!");
await fetchData(client,ts_func2);