Temporal addition, subtraction, multiplication, and division
{number,tnumber} {+, -, *, /} {number,tnumber} → tnumber
The temporal division will raise an error if the denominator is ever equal to zero during the common timespan of the arguments.
The product of two temporal floats with linear interpolation is, between two consecutive instants, a polynomial of degree two in time, and the quotient by a temporal float with linear interpolation is a ratio of two linear functions, neither of which linear interpolation can state. The result then holds the value at the instants of the arguments and, within a segment, at the extremum of the product and at one further instant of a quotient of two temporal floats, and it is linear between the instants it holds. Every value the result holds is exact, and so are its minimum and its maximum, since a quotient varies monotonically between two instants, but between the instants it holds the result is the chord of the product or of the quotient. In the examples below, the product is 3.75 at noon on January 1, where the result interpolates 3.5, and 2 divided by the temporal float is 1 at noon on January 2, where the result interpolates 1.333. The product of a temporal float by a number or by a temporal float with step interpolation, and its quotient by a number or by a temporal float with step interpolation, are linear between two instants and are exact.
SELECT tint '[2@2001-01-01, 2@2001-01-04)' + 1;
-- [3@2001-01-01, 3@2001-01-04)
SELECT tfloat '[2@2001-01-01, 2@2001-01-04)' + tfloat '[1@2001-01-01, 4@2001-01-04)';
-- [3@2001-01-01, 6@2001-01-04)
SELECT tfloat '[1@2001-01-01, 4@2001-01-04)' + tfloat '{[1@2001-01-01, 2@2001-01-02),
[1@2001-01-02, 2@2001-01-04)}';
-- {[2@2001-01-01, 4@2001-01-04), [3@2001-01-02, 6@2001-01-04)}
SELECT tint '[1@2001-01-01, 1@2001-01-04)' - tint '[2@2001-01-03, 2@2001-01-05)'; -- [-1@2001-01-03, -1@2001-01-04) SELECT tfloat '[3@2001-01-01, 6@2001-01-04)' - tfloat '[2@2001-01-01, 2@2001-01-04)'; -- [1@2001-01-01, 4@2001-01-04)
SELECT tint '[1@2001-01-01, 4@2001-01-04]' * 2; -- [2@2001-01-01, 8@2001-01-04] SELECT tfloat '[1@2001-01-01, 4@2001-01-04)' * tfloat '[2@2001-01-01, 2@2001-01-04)'; -- [2@2001-01-01, 8@2001-01-04) SELECT tfloat '[1@2001-01-01, 3@2001-01-03)' * '[3@2001-01-01, 1@2001-01-03)'; -- [3@2001-01-01, 4@2001-01-02, 3@2001-01-03) SELECT valueAtTimestamp(tfloat '[1@2001-01-01, 3@2001-01-03)' * '[3@2001-01-01, 1@2001-01-03)', '2001-01-01 12:00'); -- 3.5
SELECT 2 / tfloat '[1@2001-01-01, 3@2001-01-04)'; -- [2@2001-01-01, 0.666666666666667@2001-01-04) SELECT valueAtTimestamp(2 / tfloat '[1@2001-01-01, 3@2001-01-04)', '2001-01-02 12:00'); -- 1.3333333333333333 SELECT tfloat '[1@2001-01-01, 5@2001-01-05)' / tfloat '[5@2001-01-01, 1@2001-01-05)'; -- [0.2@2001-01-01, 1@2001-01-03, 5@2001-01-05) SELECT 2 / tfloat '[-1@2001-01-01, 1@2001-01-02]'; -- ERROR: Division by zero SELECT tfloat '[-1@2001-01-04, 1@2001-01-05]' / tfloat '[-1@2001-01-01, 1@2001-01-05]'; -- [-2@2001-01-04, 1@2001-01-05]
Return the absolute value of the temporal number
abs(tnumber) → tnumber
SELECT abs(tfloat '[1@2001-01-01, -1@2001-01-03, 1@2001-01-05]'); -- [1@2001-01-01, 0@2001-01-02, 1@2001-01-03, 0@2001-01-04, 1@2001-01-05] SELECT abs(tint '[1@2001-01-01, -1@2001-01-03, 1@2001-01-05]'); -- [1@2001-01-01, 1@2001-01-05]
Round up or down to the neareast integer
floor(tfloat) → tfloat ceil(tfloat) → tfloat
SELECT floor(tfloat '[0.5@2001-01-01, 1.5@2001-01-02]'); -- [0@2001-01-01, 1@2001-01-02] SELECT ceil(tfloat '[0.5@2001-01-01, 0.6@2001-01-02, 0.7@2001-01-03]'); -- [1@2001-01-01, 1@2001-01-03]
Round to a number of decimal places
round(tfloat,integer=0) → tfloat
SELECT round(tfloat '[0.785398163397448@2001-01-01, 2.356194490192345@2001-01-02]', 2); -- [0.79@2001-01-01, 2.36@2001-01-02]
Convert to degrees or radians
degrees({float,tfloat},normalize boolean=false) → tfloat
radians(tfloat) → tfloat
The additional parameter in the degrees function can be used to normalize the values between 0 and 360 degrees.
SELECT degrees(pi() * 5); -- 900 SELECT degrees(pi() * 5, true); -- 180 SELECT round(degrees(tfloat '[0.785398163397448@2001-01-01, 2.356194490192345@2001-01-02]')); -- [45@2001-01-01, 135@2001-01-02] SELECT radians(tfloat '[45@2001-01-01, 135@2001-01-02]'); -- [0.785398163397448@2001-01-01, 2.356194490192345@2001-01-02]
Return the value difference between consecutive instants of the temporal number
deltaValue(tnumber) → tnumber
SELECT deltaValue(tint '[1@2001-01-01, 2@2001-01-02, 1@2001-01-03]');
-- [1@2001-01-01, -1@2001-01-02, -1@2001-01-03)
SELECT deltaValue(tfloat '{[1.5@2001-01-01, 2@2001-01-02, 1@2001-01-03],
[2@2001-01-04, 2@2001-01-05]}');
/* Interp=Step;{[0.5@2001-01-01, -1@2001-01-02, -1@2001-01-03),
[0@2001-01-04, 0@2001-01-05)} */
Return the trend of a temporal float with linear interpolation, which states whether its value is increasing, constant, or decreasing, represented, respectively, by 1, 0, and -1
trend(tfloat) → tint
Note that the trend is NULL for instantaneous sequences.
SELECT trend(tfloat '[1@2001-01-01, 2@2001-01-02, 4@2001-01-03, 4@2001-01-04, 3@2001-01-05, 2@2001-01-06]'); -- [1@2001-01-01, 0@2001-01-03, -1@2001-01-04, -1@2001-01-06] SELECT trend(tfloat '[1@2001-01-01]'); -- NULL
Return the derivative over time of a temporal float in units per second
derivative(tfloat) → tfloat
The temporal float must have linear interpolation. Note that this function corresponds to the speed function for temporal points.
SELECT derivative(tfloat '{[0@2001-01-01, 10@2001-01-02, 5@2001-01-03],
[1@2001-01-04, 0@2001-01-05]}') * 3600 * 24;
/* Interp=Step;{[-10@2001-01-01, 5@2001-01-02, 5@2001-01-03],
[1@2001-01-04, 1@2001-01-05]} */
SELECT derivative(tfloat 'Interp=Step;[0@2001-01-01, 10@2001-01-02, 5@2001-01-03]');
-- ERROR: The temporal value must have linear interpolation
Return the area under the curve
integral(tnumber) → float
SELECT integral(tint '[1@2001-01-01,2@2001-01-02]') / (24 * 3600 * 1e6); -- 1 SELECT integral(tfloat '[1@2001-01-01,2@2001-01-02]') / (24 * 3600 * 1e6); -- 1.5
Return the time-weighted average
twAvg(tnumber) → float
SELECT twAvg(tfloat '{[1@2001-01-01, 2@2001-01-03), [2@2001-01-04, 2@2001-01-06)}');
-- 1.75
Return the natural logarithm and the base 10 logarithm of a temporal float
ln(tfloat) → tfloat log10(tfloat) → tfloat
The temporal float cannot be zero or negative. With linear interpolation, the result has, in each segment, the instants of the segment and the instant where the linear interpolation departs most from the logarithm, and is interpolated linearly between them
SELECT ln(tfloat '{[1@2001-01-01, 10@2001-01-02, 5@2001-01-03],
[1@2001-01-04, 1@2001-01-05]}');
/* {[0@2001-01-01, 1.363192132076512@2001-01-01 07:45:23.043236,
2.302585092994046@2001-01-02, 1.975950833017318@2001-01-02 13:22:31.148467,
1.6094379124341@2001-01-03], [0@2001-01-04, 0@2001-01-05]} */
SELECT log10(tfloat 'Interp=Step;[-10@2001-01-01, 10@2001-01-02]');
-- ERROR: Cannot take logarithm of zero or a negative number
Return the exponential (e raised to the given power) of a temporal float
exp(tfloat) → tfloat
With linear interpolation, the result has, in each segment, the instants of the segment and the instant where the linear interpolation departs most from the exponential, and is interpolated linearly between them
SELECT exp(tfloat '{[1@2001-01-01, 10@2001-01-02], [1@2001-01-04, 1@2001-01-05]}');
/* {[2.718281828459045@2001-01-01, 2447.0830569101154@2001-01-01 18:08:25.45925,
22026.465794806718@2001-01-02],
[2.718281828459045@2001-01-04, 2.718281828459045@2001-01-05]} */
SELECT exp(tfloat '{-10@2001-01-01, 0@2001-01-02, 10@2001-01-03}');
-- {0.000045399929762@2001-01-01, 1@2001-01-02, 22026.465794806718@2001-01-03}
Return the sine, cosine, or tangent of a temporal float (argument in radians)
sin(tfloat) → tfloat cos(tfloat) → tfloat tan(tfloat) → tfloat
With linear interpolation, the result of sin and cos has, in each segment, the instants of the segment and those of the extrema of the function, the result of tan is refined by bisection where the function is steep, and the result is interpolated linearly between its instants. The temporal float of tan cannot reach π/2 plus a multiple of π, at an instant or along a segment with linear interpolation
SELECT round(sin(tfloat '[0@2001-01-01, 6.283185307@2001-01-02]'), 6);
-- [0@2001-01-01, 1@2001-01-01 06:00:00.000001, -1@2001-01-01 18:00:00.000002, 0@2001-01-02]
SELECT round(cos(tfloat '[0@2001-01-01, 3.141592654@2001-01-02]'), 6);
-- [1@2001-01-01, -1@2001-01-02]
SELECT round(tan(tfloat '{0@2001-01-01, 0.785398163@2001-01-02}'), 6);
-- {0@2001-01-01, 1@2001-01-02}
SELECT tan(tfloat '[0@2001-01-01, 3@2001-01-02]');
-- ERROR: Cannot take the tangent of pi/2 plus a multiple of pi