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.
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 2 / tfloat '[1@2001-01-01, 3@2001-01-04)';
-- [2@2001-01-01, 0.666666666666667@2001-01-04)
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,2001-01-03, 5@2001-01-03,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