1-- This file and its contents are licensed under the Apache License 2.0. 2-- Please see the included NOTICE for copyright information and 3-- LICENSE-APACHE for a copy of the license. 4 5-- This file contains utilities for time conversion. 6CREATE OR REPLACE FUNCTION _timescaledb_internal.to_unix_microseconds(ts TIMESTAMPTZ) RETURNS BIGINT 7 AS '@MODULE_PATHNAME@', 'ts_pg_timestamp_to_unix_microseconds' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 8 9CREATE OR REPLACE FUNCTION _timescaledb_internal.to_timestamp(unixtime_us BIGINT) RETURNS TIMESTAMPTZ 10 AS '@MODULE_PATHNAME@', 'ts_pg_unix_microseconds_to_timestamp' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 11 12CREATE OR REPLACE FUNCTION _timescaledb_internal.to_timestamp_without_timezone(unixtime_us BIGINT) 13 RETURNS TIMESTAMP 14 AS '@MODULE_PATHNAME@', 'ts_pg_unix_microseconds_to_timestamp' 15 LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 16 17CREATE OR REPLACE FUNCTION _timescaledb_internal.to_date(unixtime_us BIGINT) 18 RETURNS DATE 19 AS '@MODULE_PATHNAME@', 'ts_pg_unix_microseconds_to_date' 20 LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 21 22CREATE OR REPLACE FUNCTION _timescaledb_internal.to_interval(unixtime_us BIGINT) RETURNS INTERVAL 23 AS '@MODULE_PATHNAME@', 'ts_pg_unix_microseconds_to_interval' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 24 25-- Time can be represented in a hypertable as an int* (bigint/integer/smallint) or as a timestamp type ( 26-- with or without timezones). In metatables and other internal systems all time values are stored as bigint. 27-- Converting from int* columns to internal representation is a cast to bigint. 28-- Converting from timestamps to internal representation is conversion to epoch (in microseconds). 29 30-- Gets the sql code for representing the literal for the given time value (in the internal representation) as the column_type. 31CREATE OR REPLACE FUNCTION _timescaledb_internal.time_literal_sql( 32 time_value BIGINT, 33 column_type REGTYPE 34) 35 RETURNS text LANGUAGE PLPGSQL STABLE AS 36$BODY$ 37DECLARE 38 ret text; 39BEGIN 40 IF time_value IS NULL THEN 41 RETURN format('%L', NULL); 42 END IF; 43 CASE column_type 44 WHEN 'BIGINT'::regtype, 'INTEGER'::regtype, 'SMALLINT'::regtype THEN 45 RETURN format('%L', time_value); -- scale determined by user. 46 WHEN 'TIMESTAMP'::regtype THEN 47 --the time_value for timestamps w/o tz does not depend on local timezones. So perform at UTC. 48 RETURN format('TIMESTAMP %1$L', timezone('UTC',_timescaledb_internal.to_timestamp(time_value))); -- microseconds 49 WHEN 'TIMESTAMPTZ'::regtype THEN 50 -- assume time_value is in microsec 51 RETURN format('TIMESTAMPTZ %1$L', _timescaledb_internal.to_timestamp(time_value)); -- microseconds 52 WHEN 'DATE'::regtype THEN 53 RETURN format('%L', timezone('UTC',_timescaledb_internal.to_timestamp(time_value))::date); 54 ELSE 55 EXECUTE 'SELECT format(''%L'', $1::' || column_type::text || ')' into ret using time_value; 56 RETURN ret; 57 END CASE; 58END 59$BODY$; 60 61CREATE OR REPLACE FUNCTION _timescaledb_internal.interval_to_usec( 62 chunk_interval INTERVAL 63) 64RETURNS BIGINT LANGUAGE SQL IMMUTABLE PARALLEL SAFE AS 65$BODY$ 66 SELECT (int_sec * 1000000)::bigint from extract(epoch from chunk_interval) as int_sec; 67$BODY$; 68 69CREATE OR REPLACE FUNCTION _timescaledb_internal.time_to_internal(time_val ANYELEMENT) 70RETURNS BIGINT AS '@MODULE_PATHNAME@', 'ts_time_to_internal' LANGUAGE C VOLATILE STRICT; 71 72CREATE OR REPLACE FUNCTION _timescaledb_internal.cagg_watermark(hypertable_id INTEGER) 73RETURNS INT8 AS '@MODULE_PATHNAME@', 'ts_continuous_agg_watermark' LANGUAGE C STABLE STRICT; 74 75CREATE OR REPLACE FUNCTION _timescaledb_internal.subtract_integer_from_now( hypertable_relid REGCLASS, lag INT8 ) 76RETURNS INT8 AS '@MODULE_PATHNAME@', 'ts_subtract_integer_from_now' LANGUAGE C STABLE STRICT; 77