Theory of Time Series Expressions

The theory of time series expressions is outlined in the following sections. Because time series expressions build on (non-time series) SQL expressions, a basic familiarity with SQL expressions is assumed – see SQL in InfoAsset Manager for more details.

Time series expressions can be used in SQL queries in InfoAsset Manager for the purpose of analysing and extracting individual values and statistics from time series data (TSD) streams that are linked to objects in a network.

In an SQL query, time series data is loaded using the TSLOAD function and each time series has a domain that is specified explicitly using arguments to that function. The units of measurement are specified as an argument of the TSLOAD function.

In a derived stream expression, time series data is loaded using the TSINPUT function and all time series required to satisfy a user’s request (e.g. for display or to answer a question) are defined over the same domain which is determined by the request (plus an allowance for resampling).

Quick review of conventional (numeric) expressions

The SQL engine can evaluate expressions composed of operators, functions and simple numeric values (and also boolean, date and string values, but these are less relevant here). It can also assign values to variables and use these variables in expressions. The value of an expression containing multiple semicolon-separated clauses is the value of the last clause, with the previous clauses used to calculate intermediate variables. Examples of expressions using various operators and functions are:

3 + 4
SIN(3) – COS(4)
SET $A = 3; SET $B = $A + 4; 2.4 * LOG($A * $B) – 3

An expression is evaluated in the context of a specific object in a network. In InfoAsset Manager, it can be any object in the network that can have links to time series data. The expression can include the names of fields of this object and these are replaced by the values stored in those fields when the expression is evaluated. It can also include fields of linked objects . Examples are:

3 + user_number_1
LOG(user_number_1) + LOG(user_number_5)

Time series data as a new type of value

To work with time series in expressions, we treat an individual, finite sequence of time series data points as a single value of a new type, which we call "time series data". This new type is on a par with the existing types (numeric, boolean, date, string and null) and can be used in expressions in very much the same way. Typically, the value of an expression containing time series data values will itself be a time series data value.

Note that in the discussion that follows, we are careful to distinguish between a time series data value, which is a value that encapsulates the whole of a finite period of a time series, and an individual (number or null) value taken from a particular data point within that time series.

The theory of time series expressions consists of defining the properties of time series data values and the way in which the different operators and functions act on these values to generate new values. The following sections provide these definitions.

Properties of time series data values

Time series data values used in an expression have the following properties:

Each time series data value in an expression is defined on the same, finite, time domain defined by a time zone (e.g. "(UTC+10:00) Canberra, Melbourne, Sydney") and an absolute start and end time (e.g. 2015-01-12 04:45:00 to 2015-02-06 14:20:00).
The time domain of a time series data value is determined by the data-loading function and/or the context in which the expression is evaluated, as follows:

For data loaded by the TSLOAD function, the time zone is the machine time zone and the start and end time are specified as arguments to that function in local clock time (i.e. including daylight saving).
For numeric values that are automatically converted into time series data values, the time domain is large enough to encompass all data already loaded via TSLOAD.

Internally, a time series data value comprises an array of pairs of [time stamp, number or null] that define a possibly-discontinuous, piece-wise linear or step function within the time domain.
The time stamps are strictly monotonic increasing in the array.
The start and end of the time domain are always within the range of times in the array. If necessary, data points are added at the start and/or end of the time domain to ensure this is true. Input data from a TSD will run from the first point at or before the start of the domain to the last point at or after the end of the domain.
All time stamps are in the local standard time of the specified time zone. When sourcing data from a TSD (which uses UTC internally) a fixed offset is applied to convert to local standard time.
The internal representation of time stamps cannot be accessed directly. If calculations based on date-time are required, the TSDATETIME function can be used to create a series whose values are the numeric equivalent of its time stamps (i.e. double-precision number of days since 00:00 30-Dec-1899) and which can be used with SQL functions that work with dates.
The numeric values in the array are represented as double-precision numbers, with null represented by a "quiet NaN" value.
A time series data value has an "interpolation" property that determines whether the function is linear (interpolation="linear") or step-wise (interpolation="extend" or "step"). This is used when an operator or function needs to obtain a numeric value for an intermediate time which does not appear in the array.
The units of measurement are user-defined in the sense that the user specifies the desired units at the point that the time series data value is constructed( see the TSLOAD function for details).
A time stamp paired with a null means that the value at that time-stamp is unknown/missing. This has implications for how values between data points are interpolated and in turn affects calculations that include null values or statistics of intervals that include null periods. This is discussed further below.

How null values are generated within a time series data value

Null-valued data points can appear in a time series for any of the following reasons:

The data comes from a TSD stream and that stream includes explicitly-entered, null-valued points.
The data comes from a TSD stream and the gap between two data points is greater than the data interval specified for the stream. In this case, a null-valued data point is automatically inserted at the time at which the data stream becomes invalid (i.e. data_interval seconds after the previous valid data point).
The data comes from a TSD stream and the first data in the stream is after the start of the time domain. In this case, a null-valued data point is automatically inserted at the start of the time domain.
The data comes from a TSD stream and the last data in the stream is before the end of the time domain. In this case, a null-valued data point is automatically inserted at the end of the time domain.
A non-time series null value is converted to a time series data value (see Conversion rules)
An operator or function generates a null-valued data point for one of the reasons documented for that function (the most common reason being that one of the inputs is null at that time)

In general, operators and functions do not generate multiple sequential null-valued data points. Once a null-valued data point has been generated, subsequent null-valued data points are skipped until the next data point that has a non-null value is reached, or the end of the time domain is reached.

Interpolation rules and the effect of null values

As noted above, a time series defines a piece-wise linear or step function within the time domain, according to whether the interpolation property is "linear" or "extend" (note that "step" can be used as a synonym for "extend"). In the absence of nulls, this means that numeric values are well-defined at all times within the domain and that time-integrals and similar operations over arbitrary time periods can be computed.

If a data point has a null value, then values at intermediate times between this data point and the next non-null data point will be treated as null. Additionally, linear interpolation between a non-null and null value is not possible, so for a linear time series, intermediate values after the preceding data point are also treated as null. Thus null-valued data points create periods of null value within the time series.

Functions and operators that take values from specific times within such periods have their own rules about how to handle nulls, as discussed in the sections describing operators and functions. Typically, but not always, the resulting value is also null.

Functions that calculate statistics (e.g. mean, min, max) over specific intervals will usually calculate the statistic over the non-null periods in the interval, if any, returning null only if the entire interval is null. Statistics about null data are also available (e.g. countnonnull, coverage). Some statistics (e.g. count, percentile) work only on individual data points (i.e. not weighted by time). See TSRESAMPLE for more details.

Conversion rules for time series data values

A time series data value will never be automatically converted to any other type of value.

Other types of value will be automatically converted to a time series value as needed for use in an operator or function (see the following sections). The conversion rules are:

Null value converts to a time series data value which has only a null value at the start and end times.
Numeric value converts to a time series data value that has interpolation="extend" and two points, one at the start time with value equal to the specified number and one at the end time with a null value.
Date value converts to its numeric equivalent, which is the number of days (and fractional days) since 00:00 30-Dec-1899.
Other types convert first to a number (following the usual conversion rules) and then to time series data value as described above.

Network object fields available for use in time series data expressions

In a time series data expression that is part of an SQL query, any object field that is normally usable for SQL can be used, provided it is of an appropriate type (usually numeric).

Units in time series data expressions

Values loaded from network object fields used in SQL queries are evaluated using user units.

The approach taken for time series data values and expressions is to specify the required units explicitly where needed. Thus TSLOAD has an argument to specify the required units.

It is the responsibility of the user to ensure that all numeric values used in the expression are correct for the chosen units.

Constructing time series data values

Unlike a simple numeric value, which can be entered in an expression as a literal such as 3.14159 or obtained from a field of an object in the network, a time series data value can only be created as the result of an expression that involves time series data values, or as the result of a function that constructs a time series data value using parameters that are simple values. The most important such functions are the TSLOAD and TSPOINTS constructor functions. See Specialised functions for use only with time series data values for more information.

Standard operators that can be used with time series data values

Any of the standard set of arithmetic, logical or comparison operators, unary or binary, may be applied to a time series data value or pair of values. The operators are:

Unary arithmetic:

- (negate)

Unary logical:

NOT
IS NULL
IS NOT NULL

Binary arithmetic:

+ (add)
- (subtract)
* (multiply)
/ (divide)
^ (raise to the power of)
% (modulo)

Binary logical:

Binary comparison:

< (less than)
> (greater than)
= (equal)
<> (not equal)
<= (less than or equal)
>= (greater than or equal)

A binary operator may be applied to a time series data value and a non-time series data value. The latter is first converted to a time series data value as described under conversion rules above.

The output value of an operator acting on time series data value(s) is computed as follows:

All the distinct times associated with all data points in all of the input time series data value(s) are identified and placed in order.

For each such time:

The numeric value at that time is obtained from each of the input time series data values, applying appropriate interpolation rules and treatment of null values.
The numeric values thus obtained are combined using the normal rules for the operator, including the treatment of null values. For logical operators, zero input is treated as false and non-zero non-null input is treated as true. For logical and comparison operators, false output is converted to zero and true output is converted to one. If either input is null, the result is null, with the following exceptions:

IS NULL and IS NOT NULL work as one would expect
= is true if both arguments are NULL, false if only one argument is NULL
<> is true if only one argument is NULL, false if both arguments are NULL
OR is true if the non-NULL argument is non-zero, otherwise false

The resulting numeric value is placed in the output time series data value as a data point at that time (except that consecutive null data points are dropped if not at the end of the time domain).

If all the input time series data values use step/extend interpolation, the result also uses step/extend. Otherwise it uses linear.

Standard functions that can be used with time series data values

A wide variety of the standard SQL functions can be used with time series data values, as described below.

If all the input time series data values for a function use extend/step interpolation, the result also uses extend/step. Otherwise it uses linear.

Simple functions taking numeric arguments

All of the standard SQL functions that take one or more numeric arguments and that yield a numeric result can also be used with time series data arguments and will yield a time series data value as a result. The rules described above for operators apply for converting arguments to time series data values and for computing the times and values of individual data points. These functions are:

INT, FLOOR, CEIL, ABS
All trigonometric and logarithmic functions

Functions that work with lists

The list-related functions LOOKUP, INDEX, RINDEX and MEMBER can take a time series data value as their first argument and a list as their last argument, returning a time series data value. Normally the list will be a number list. If used with date or string lists, then for each individual data point value, LOOKUP will convert the date/string to a number, MEMBER and RINDEX will always return 0 (not present) and INDEX will always return NULL.

Functions that work with dates

Functions that work with dates and have a numeric or Boolean result can also be used with time series data values. These functions are:

YEARPART
MONTHPART
DAYPART
DAYOFWEEK

DAYOFYEAR
DAYSINYEAR
DATEPART
TIMEPART

INYEAR
INMONTH
INYEARS
INMONTHS

The values in the time series are treated as being the numeric representation of dates (e.g. as set by the TSDATETIME function) and the various functions operate on these dates as documented. Note that TIMEPART is always truncated to a whole number of minutes.

Conditional functions

The standard conditional SQL functions IIF and NVL also work for time series data values. Unlike the other functions, they do not generate an output data point for every possible input time. Instead:

IIF uses times from the second argument only during periods when the first argument is true (non-zero and non-null) and uses times from the third argument only during periods when the first argument is false (zero). This avoids introducing unnecessary times from data points in the unused time series. During periods when the first argument is null, the result is null.
NVL uses times from the second argument only during periods when the first argument is null. This avoids introducing unnecessary times from data points in the unused time series.

Other standard functions

If standard SQL functions other than those listed above are used with time series data arguments, the result is a null value.

Specialised functions for use only with time series data values in InfoAsset Manager

The following functions can be used only to create or manipulate time series data values. They cannot be used in "normal" SQL expressions and queries.

TSLOAD
TSPOINTS
TSRESAMPLE
TSAGGREGATE
TSAPPLYPROFILE
TSBEFORE
TSDATETIME
TSLAG
TIDE

TSLOAD

TSLOAD (TSD_link_description, units, start_time, end_time)

Creates a time series from a linked TSD stream, identified by its description.

This function should be used only in an SQL query.

The TSD link description must be specified as an explicit string, or as a field or expression that evaluates to a string. The time series data links of the current object are then checked to find the first link that has a description that matches the string.

If no such link is found, or the link does not specify a TSD stream name, or the named stream is not found in the appropriate TSD object, the function returns null.

The appropriate TSD object is searched for within the database using the TSD path specified in the link or (if that is blank) the default TSD path set for the network or (if that is blank) the default TSD path set for the database. If the resulting path is blank or the TSD object cannot be found using the path, an error is raised.

The units must be specified as a string such as "mm" or "ft" or "" and the specified units must be compatible with the units (quantity) of the TSD stream. If not, an error is raised.

The start_time and end_time must be specified as dates or as numeric values that are equivalent to dates (i.e. a number of days after 00:00 30-Dec-1899). The latter allows for calculated dates such as NOW() - 7. These dates are interpreted as local clock time (i.e. including daylight saving) in the time zone of the machine. If these values cannot be interpreted as dates, or the end_time is at or before the start_time, the function returns null.

The returned value is obtained from the TSD stream for the specified time domain, noting that:

The time domain is defined by the start_time and end_time arguments
Numeric values within the time series are converted to the specified units
Data points with null values are inserted as needed to directly represent missing periods at the start, end or between observed values (where the gap is greater than the data interval of the TSD stream)
The interpolation rule for the time series data value is set to be the same as for the TSD stream
If necessary, numeric values are calculated for the start and end point of the domain using the appropriate interpolation rule

TSPOINTS

TSPOINTS(interval_unit, interval_multiplier, date_origin, time_of_day_origin, daylight_saving)

Constructs a time series containing data points that have a specified, repeating time interval. All data points in this series are given the same value (1.0). Intervals are not necessarily of equal duration (e.g. days adjusted for daylight saving; months; years). The created time series uses step/extend interpolation.

The main uses of such a series are to define the times for resampling of other series (using TSRESAMPLE) or to generate profiles that repeat for each interval (using TSAPPLYPROFILE).

When used in an SQL query, it will use a time domain that encompasses the time domains of all data previously loaded by calls to TSLOAD. It is therefore important that TSPOINTS is called after all relevant calls to TSLOAD.

The interval unit must be one of the following:

"s" or "second"
"m" or "minute"
"h" or "hour"
"d" or "day"
"w" or "week"
"mon" or "month"
"y" or "year"

The chosen interval unit is multiplied by the interval multiplier to give the interval between data points. The multiplier may be fractional if the interval unit is a day or smaller and must be greater than or equal to 0.001.

If daylight saving is true, adjustments for daylight saving are made when crossing a transition between standard time and daylight saving time in the time zone of the domain. Daylight saving adjustment is only carried out if the interval unit is an hour or greater and the interval multiplier is a whole number. This can be used to create daily time stamps that are at the same local clock time every day (e.g. 09:00). Because InfoAsset Manager runs are always in local standard time, the visible time stamp is then one hour earlier during daylight saving (e.g. 08:00).

The procedure for aligning the time stamps of the data points is as follows:

If the interval unit is hours or smaller, or date_origin is null or an empty string, the date origin is taken to be the day before the day that contains the start of the time domain. Otherwise, the string or date provided is used and must be before the date of the start of the time domain. Only the date part of the date_origin is used.
If the time origin is null or an empty string, it is taken to be midnight ("00:00:00"). Otherwise, it must be a time-of-day string (e.g. "09:00", "00:00:00.1"). The length of time after midnight must be less than the interval.
The date origin and time origin are combined and used as the time-stamp of the "origin" data point of the series. The time stamps of all other data points are calculated as successors to this origin using the specified interval (this is why the origin needs to be before the start of the time domain).
The actual first data point in the created series will be the data point at or immediately before the start of the time domain, while the last point will be at or immediately after the end of the time domain.

The parameters of this function are somewhat complicated due to the wide range of possible time scales and possible variations in the length of intervals. The following examples of different intervals should help to make them clearer:

100 milliseconds
TSPOINTS("s", 0.1, "", "", 0)
30 seconds
TSPOINTS("s", 30, "", "", 0)
15 minutes
TSPOINTS("m", 15, "", "", 0)
3 hours
TSPOINTS("h", 3, "", "", 0)
12 hours, at 09:00 and 21:00 local clock time
TSPOINTS("h", 12, "", "09:00", 1)
Daily at midnight local standard time
TSPOINTS("d", 1, "", "", 0)
Weekly, every Wednesday at 09:00 local clock time (noting that 5 Jan 2000 was a Wednesday)
TSPOINTS("w", 1, "2000-01-05", "09:00", 1)
Quarterly, at midnight local clock time on the first day of the month
TSPOINTS("mon", 3, "2000-01-01", "", 1)
Annually, at midnight local clock time on 1 July each year
TSPOINTS("y", 1, "2000-07-01", "", 1)

Note that it is not possible to create diary-style or specialised intervals (e.g "3rd Wednesday of every month"; "accounting month"; "financial quarter").

TSRESAMPLE

TSRESAMPLE(value_series, time_stamp_series, statistic, window_option, left_secs, right_secs, statistic_factor, output_interpolation)

Resamples the values in value_series using the time stamps of the non-null, non-zero data points in time_stamp_series, calculating the value for a window around each time stamp using the specified statistic and window_option.

Null or zero-valued data points in the time_stamp_series are used to generate output data points at the same time stamps with these points having null values. This is useful for calculating statistics of event periods where each event is represented by a non-null, non-zero data point at the start of the event and a null or zero data point at the end of the event.

The resulting time series uses the specified output_interpolation.

The window options are:

1 – window is the interval between this time stamp and the next time stamp
0 – window is from left_secs seconds before this time stamp to right_secs seconds after it
-1 – window is the interval between the preceding time stamp and this time stamp

The statistics that may be calculated for values in the window are:

"MEAN" - time-mean value
"TMEAN" - triangular-weighted time-mean (used mainly for smoothing)
"INTEGRAL" - time-integral (with time measured in seconds)
"SDEV" - standard deviation
"FIRST" - first non-null value
"LAST" - last non-null value

"DUR" - duration(length) of the time window in seconds
"DVDT" - time derivative over the window
"MAX" - maximum value
"MIN" - minimum value
"MAXTIME" - time (in seconds after start of window) at which maximum occurs
"MINTIME" - time (in seconds after start of window) at which minimum occurs

"COUNT" - the count of data points (including those with null values)
"COUNTNONNULL" - the count of data points (excluding those with null values)
"TOTAL" - the sum of the values from each data point
"COVERAGE" - the percentage of time during which the value is non-null
"PERCENTILE" - value of the specified percentile of individual values in the window
"BWDPERCENTILE" - percentile calculated as prescribed by EU Bathing Water Directive

Note that:

MEAN, TMEAN, INTEGRAL and SDEV are based on time-integration of the values in the window according to the interpolation rule for the value series. If the interpolation rule is linear or step/extend then there need not be any data points from the value series within the window. For delta interpolation, all non-null points are given a unit weighting (thus INTEGRAL is equivalent to TOTAL and MEAN is a simple average)
PERCENTILE and BWDPERCENTILE statistics are based on the values at non-null points within the time window (i.e. no time-weighting)
If there are periods of null value within the window, most statistics are calculated over the non-null periods, unless the entire window is null. The exceptions are:
- DUR is calculated from window start to window end, regardless of whether either end is null.
- DVDT is calculated from window start to window end. If either end is null, the result is null.
- COUNT counts both null and non-null data points
- COVERAGE is the non-null percentage of the entire window
The window is treated as being half-open on the right – that is, a time stamp that defines the right end of the window is not treated as falling within the window. This means that:

The COUNT of a window which is the interval between successive data points is 1, not 2. Likewise, the TOTAL, PERCENTILE and BWDPERCENTILE and any statistics using delta interpolation do not include the value of the second data point. This avoid double-counting of data points and their values.
For a extend/step series, the LAST value in a window which is the interval between two time stamps is the value from the first time stamp. This reflects the fact that a step/extend series is a discontinuous function.

For points near the start or end of the time domain, it is possible for the time window to extend to times outside the domain. The value series might or might not have data at these times (depending on how it overlaps the time domain) and times with no data are treated as null. Thus statistics at or near end points can be different from what they would be at the same times with a wider time domain.

For most of the above statistics, the statistic parameter is simply a multiplier that is applied after calculating the statistic. This is useful for applying a scaling factor and/or converting time from seconds to some other unit (for INTEGRAL, DUR, DVDT, MINTIME or MAXTIME). A value of 1.0 should be specified if no scaling is required. For PERCENTILE and BWDPERCENTILE, the statistic parameter is the percentile to be calculated (e.g. 95, 99).

The output interpolation must be one of

"linear"
"extend"/"step"

Resampling buffer and chained (continuous) simulation

There is no resampling buffer in InfoAsset Manager as the user explicitly specifies the time periods to load (using TSLOAD) and the time periods over which to calculate results (using TSAGGREGATE). The user must also ensure that the former loads sufficient data for the latter.

TSAGGREGATE

TSAGGREGATE(value_series, start_time, end_time, statistic, statistic_factor)

Calculates the specified statistic for values in value_series using the specified time window.

This function returns either null or a single numeric value – not a time series data value, and is intended for use in an SQL query.

The available statistics and the meaning of the statistic factor are exactly as described for the TSRESAMPLE function, with the following exception:

The MAXTIME and MINTIME statistics return numbers that can be interpreted as dates (i.e. days since 00:00 30-Dec-1899) in the local clock time (i.e. including daylight saving) of the machine.

TSAPPLYPROFILE

TSAPPLYPROFILE(input_series, normalize, divide_by_duration, multiplier, output_interpolation, number_list)

This function takes a profile, defined as a number list of N values, and applies it to every data point in the input series that has a non-zero, non-null value, as follows:

If the normalize option is true, the profile in the list is first normalized (i.e. time-mean value set to 1.0).
The time interval between the current data point and the next data point is divided into either N (if output_interpolation is step/extend) or N-1 (if output_interpolation is linear) equal intervals
An initial value at each data point in the sub-divided interval (starting at the time of the original point) is computed by multiplying the value at the original point by the value for the corresponding entry in the list. If output_interpolation is linear and the subsequent value in the original series is zero or null, the N"th value in the list is applied at this time.
If divide_by_duration is true, these initial values are then divided by the duration of the interval (in seconds). This can be used to convert an original value that is a total over the original interval (e.g. mm of rain) into an intensity (e.g. mm/s).
Finally, each value is multiplied by the specified multiplier. This can be used to convert from, say, mm/s to mm/hr.

For each input data point that has a null or zero value, an output data point with a null value is generated. If the output uses step/extend interpolation, the time stamp for this output point will be the same as the input. If the output uses linear interpolation, there will already be an output point at this time stamp (to allow interpolation up to the end of the interval) and so the null-valued point is output with a time stamp half way between this and the next input time stamp – this prevents interpolation between periods that represent distinct events.

The resulting time series uses the specified output interpolation, which must be one of:

"linear"
"extend" or "step"

TSBEFORE

TSBEFORE(date_time)

Constructs a series that is true (1.0) before the specified date-time and false (0.0) thereafter, with interpolation="extend". The resulting time series data value will contain either 3 data points (if the specified date-time is inside the time domain) or 2 data points (if the specified date-time is outside or on a boundary of the time-domain).

TSDATETIME

TSDATETIME(data_series, daylight_saving, output_interpolation)

Constructs a series with time stamps that match the points in the input data series and values that are the numeric equivalents of these time stamps. If daylight saving is non-zero, daylight saving adjustment is applied when converting the time stamps to numeric values.

The numeric equivalent of a time stamp is the number of days (including fractional days) since 00:00 30-Dec-1899. This is not usually of direct interest but can be used for:

Calculating the time difference (in days) from a specific date-time, such as the run origin
Extracting particular components of the date-time using SQL functions that work on date-times, such as YEARPART, MONTHPART, DAYPART, TIMEPART, DAYOFWEEK, DAYOFYEAR, DAYSINYEAR

Daylight saving adjustment should not be applied if the values are to be used for calculating time differences. It is intended for use in converting these values to time of day or day of week in local clock time.

The resulting time series uses the specified output interpolation, which must be one of

"linear"
"extend" or "step"

TSLAG

TSLAG (input_series, lag_seconds)

Constructs a series containing the same data points as the input series, but with time stamps shifted forward (lagged) by the specified number of seconds. A negative lag may be specified to shift the time stamps backwards. When used in a TVD connector expression, the resampling buffer for the TVD connector (in minutes) should be set to be at least as large as the lag time (in seconds) so that data from before the start of the simulation can be shifted into the simulation period (otherwise, the first lag_seconds of the simulation period will contain null data).

TIDE

TIDE(constituent_name, amplitude, phase_lag, timestep)

Constructs a time series for a specified tidal constituent, amplitude and phase lag, using linear interpolation.

constituent_name – a string which identifies a tidal constituent. 28 constituents, derived from the UK Admiralty Tide Tables (time zone referenced by Greenwich Mean Time), are supported and are described in the Tidal constituents table.
amplitude – the mean amplitude of the constituent (taken over a 19-year tidal epoch) in units such as metres.
phase_lag – the phase lag for each tidal constituent, in degrees. This is the phase lag of the constituent relative to the phase of the equilibrium tide at Greenwich, where the phase at Greenwich is calculated using local standard time (i.e. not converted to GMT).
timestep – the time interval between output data points, in minutes. The timestep must not be less than 0.1 minutes.

The first output data point is at the start of the time domain and the last is at or immediately after the end of the time domain.

The choice of whether to reference phase lag to local standard time or GMT is a matter of convention. When using the TIDE function for time zones other than GMT, it is important to be clear that TIDE uses the convention that phase lags are referenced to local standard time. If a phase lag (G) referenced to GMT is provided, it can be converted to a phase lag (g) referenced to local standard time in time zone (UTC + N hours) as follows:

g = G - aN

Where a is the angular speed of the constituent in degrees/hour.

Notes on using NULL in expressions

The behaviour of NULL is slightly complex:

Essentially NULL is an 'I have no value' value.
The general rule for SQL is that any operation involving anything and NULL gives the answer NULL e.g. 3 + NULL is NULL. Important exceptions to this rule are the operators OR, IS NULL and IS NOT NULL and the function NVL (OR treats NULL as false, while NVL is used to detect and replace NULL values).

Theory of Time Series Expressions

Contents