Functions on Timestamps

Create and populate Baggage schema

If you want to follow along with the examples, download the script baggageschema_loaddata.sql and run it as shown below. This script creates the table used in the example and loads data into the table.

Start your KVSTORE or KVLite and open the SQL.shell.

java -jar lib/kvstore.jar kvlite -secure-config disable
 java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore

Using the load command, run the script.

load -file baggageschema_loaddata.sql

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 datatype.

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 in 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 flightsLegs 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). The bagArrivalDate should have a value between today and the last 7 days. For every record in thebagInfo 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 one week. The count function determines the total number of bags in this time period.

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 is bagInfo 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.

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".

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?

To determine the duration in milliseconds, use the

timestamp_diff

function.

SELECT $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;

Explanation: In an airline application every customer can have 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.

Output:

{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T07:00:00Z","diff":11820000}
{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T07:23:00Z","diff":10440000}
{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T08:23:00Z","diff":6840000}

To determine the duration in days or hours or minute , pass the result of the timestamp_diff function to the get_duration function.

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;

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"}

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?

To determine the duration in milliseconds, 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);

Explanation: In the baggage data, every flightLeg has an actions array. There are three different actions in the actions 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.

Output:

{"flightNo":"BM572","checkinTime":"2019-03-02T03:28:00Z","bagScanTime":"2019-03-02T04:52:00Z","diff":-5040000}

To determine the duration in days or hours or minutes, pass the result of the timestamp_diff function to the get_duration function.

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);

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?

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;

Explanation: In an airline application every customer can have 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.

Output:

{"flightDate":"2019-03-12T03:00:00Z","estimatedArrival":"2019-03-12T16:00:00Z","diff":46800000}
{"flightDate":"2019-03-12T03:00:00Z","estimatedArrival":"2019-03-12T16:40:00Z","diff":49200000}

To determine the duration in days or hours or minutes, pass the result of the timestamp_diff function to the get_duration function.

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;

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"}

Miscellaneous Timestamp functions

year function

Returns the year for the given timestamp. The returned value is in the range -6383 to 9999. If the argument is NULL or empty, the result is also NULL or empty.

Syntax:

integer? year(timestamp?)

Semantics:

timestamp: This function expects a timestamp as the input argument.
return type: integer

month function

Returns the month for the given timestamp, in the range 1 ~ 12. If the argument is NULL or empty, the result is also NULL or empty.

Syntax:

integer? month(timestamp?)

Semantics:

timestamp: This function expects a timestamp as the input argument.
return type: integer

day function

Returns the day of month for the timestamp, in the range 1 ~ 31. If the argument is NULL or empty, the result is also NULL or empty.

Syntax:

integer? day(timestamp?)

Semantics:

timestamp: This function expects a timestamp as the input argument.
return type: integer

hour function

Returns the hour of day for the timestamp, in the range 0 ~ 23. If the argument is NULL or empty, the result is also NULL or empty.

Syntax:

integer? hour(timestamp?)

Semantics:

timestamp: This function expects a timestamp as the input argument.
return type: integer

minute function

Returns the minute for the timestamp, in the range 0 ~ 59. If the argument is NULL or empty, the result is also NULL or empty.

Syntax:

integer? minute(timestamp?)

Semantics:

timestamp: This function expects a timestamp as the input argument.
return type: integer

second function

Returns the second for the timestamp, in the range 0 ~ 59. If the argument is NULL or empty, the result is also NULL or empty.

Syntax:

integer? second(timestamp?)

Semantics:

timestamp: This function expects a timestamp as the input argument.
return type: integer

millisecond function

Returns the fractional second in millisecond for the timestamp, in the range 0 ~ 999. If the argument is NULL or empty, the result is also NULL or empty.

Syntax:

integer? millisecond(timestamp?)

Semantics:

timestamp: This function expects a timestamp as the input argument.
return type: integer

microsecond function

Returns the fractional second in microsecond for the timestamp, in the range 0 ~ 999999. If the argument is NULL or empty, the result is also NULL or empty.

Syntax:

integer? microsecond(timestamp?)

Semantics:

timestamp: This function expects a timestamp as the input argument.
return type: integer

nanosecond function

Returns the fractional second in nanosecond for the timestamp, in the range 0 ~ 999999999. If the argument is NULL or empty, the result is also NULL or empty.

Syntax:

integer? nanosecond(timestamp?)

Semantics:

timestamp: This function expects a timestamp as the input argument.
return type: integer

Example 1: Miscellaneous timestamp functions

In an airline application, it is beneficial to the passengers to have a quick summary of their upcoming travel details. You can use miscellaneous time functions to get consolidated travel details of the passengers from the BaggageInfo table.

SELECT DISTINCT
$s.fullName,
$s.bagInfo[].flightLegs[].flightNo AS flightnumbers,
$s.bagInfo[].flightLegs[].fltRouteSrc AS From,
concat ($t1,":", $t2,":", $t3) AS Traveldate
FROM baggageinfo $s, $s.bagInfo[].flightLegs[].flightDate AS $bagInfo,
day(CAST($bagInfo AS Timestamp(0))) $t1,
month(CAST($bagInfo AS Timestamp(0))) $t2,
year(CAST($bagInfo AS Timestamp(0))) $t3;

Explanation:

You can use the time functions to retrieve the travel date, month, and year. The concat function is used to concatenate the retrieved travel records to display them in the desired format on the application. You first use CAST to convert the flightDates to a TIMESTAMP and then fetch the date, month, and year details from the timestamp.

Output:

{"fullName":"Adam Phillips","flightnumbers":["BM604","BM667"],"From":["MIA","LAX"],"Traveldate":"1:2:2019"}

{"fullName":"Adelaide Willard","flightnumbers":["BM79","BM907"],"From":["GRU","ORD"],"Traveldate":"15:2:2019"}

The query returns the flight details which can serve as a quick look-up for the passengers.

week function

Returns the week number within the year where a week starts on Sunday and the first week has a minimum of 1 day in this year, in the range 1 ~ 54. If the argument is NULL or empty, the result is also NULL or empty.

Syntax:

integer? week(timestamp?)

Semantics:

timestamp: This function expects a timestamp as the input argument.
return type: integer

isoweek function

Returns the week number within the year based on IS0-8601, where a week starts on Monday and the first week has a minimum of 4 days in this year, in range 0 ~ 53. If the argument is NULL or empty, the result is also NULL or empty.

Syntax:

integer? isoweek(timestamp?)

Semantics:

timestamp: This function expects a timestamp as the input argument.
return type: integer

current_time_millis function

Returns the current time in UTC, as the number of milliseconds since January 1, 1970 UTC.

Syntax:

long current_time_millis()

Semantics:

This function does not expect any input argument.
return type: long

current_time function

Returns the current time in UTC, as a timestamp value with millisecond precision.

Syntax:

timestamp(3) current_time()

Semantics:

This function does not expect any input argument.
return type: timestamp(3)

Example 2: Miscellaneous timestamp functions

In an airline application, a few customers travel very frequently and are entitled to frequent flier miles rewards. You can determine the time lapse between the last travel date of a passenger and the current date to assess if they can be considered for such a reward program.

SELECT
$s.fullName,
$s.contactPhone, 
week(CAST($bagInfo.flightLegs[1].flightDate AS Timestamp(0))) AS TravelWeek,
isoweek(CAST($bagInfo.flightLegs[1].flightDate AS Timestamp(0))) AS ISO_TravelWeek,
get_duration(timestamp_diff(current_time(), CAST($bagInfo.flightLegs[1].flightDate AS Timestamp(0)))) AS LastTravel
FROM baggageinfo $s, $s.bagInfo[] AS $bagInfo;

Explanation:

You can use the current_time function to get the current time. To determine the timespan between the last travel date and the current date, you can supply the current time to the get_duration/timestamp_diff function along with the last travel time. For more details on timestamp_diff and get_duration functions, see timestamp_diff and get_duration functions.

Output:

{"fullName":"Adelaide Willard","contactPhone":"421-272-8082","TravelWeek":7,"ISO_TravelWeek":7,"LastTravel":"1453 days 6 hours 20 minutes 56 seconds 601 milliseconds"}

{"fullName":"Adam Phillips","contactPhone":"893-324-1064","TravelWeek":5,"ISO_TravelWeek":5,"LastTravel":"1451 days 23 hours 19 minutes 39 seconds 543 milliseconds"}

The week function determines the travel week, isoweek function provides the ISO week details. You use the current_time function to calculate the current time. Use the timestamp_diff function to calculate the time difference between the current time and the last flight date. You first use CAST to convert the flightDates to a TIMESTAMP and then fetch the day, month, and year details from the timestamp. Since the timestamp_diff function returns the number of milliseconds between two timestamp values, you then use the get_duration function to convert the milliseconds to a duration string.

The get_duration function converts the milliseconds to days, hours, minutes, seconds, and milliseconds based on the return value. The following conversions are considered for calculation purposes:

1000 milliseconds = 1 second
60 seconds = 1 minute
60 minutes = 1 hour
24 hours = 1 day

For example: If the timestamp_diff function returns the value 129084684821 milliseconds, the get_duration function converts it correspondingly to 1494 days 52 minutes 4 seconds 687 milliseconds.