I am new to vertica.
I am trying to get datetime of the seconds provided.
I have seconds value from 1980-01-01.
when I try like this:
\set interval '''1199283171.887953002212558175021 SECOND'''
SELECT DATE('1980-01-01') + INTERVAL :interval
I am getting my output:
2018-01-01 14:12:51.887953
(One of the seconds value set to variable interval. Trying in Linux putty)
But I want to have it for my 100 records in table. This is just for single input. I tried but not getting it.
How can I do it for all values?
If I have understood you right, you have one hundred times a float value with seconds, meaning the number of seconds passed since '1980-01-01 00:00:00' probably UTC.
I found three ways of doing this - as you can see here, with a 10-row example table.
I was just lazy and added << n >> times 3600 seconds to your initial seconds value. Makes it readable, as just the hour changes in the resulting timestamp. As you can see, the expressions all return the same.
Would that help?
WITH input(secs) AS (
SELECT 1199283171.887953002212558175021 + 0 * 3600
UNION ALL SELECT 1199283171.887953002212558175021 + 1 * 3600
UNION ALL SELECT 1199283171.887953002212558175021 + 2 * 3600
UNION ALL SELECT 1199283171.887953002212558175021 + 3 * 3600
UNION ALL SELECT 1199283171.887953002212558175021 + 4 * 3600
UNION ALL SELECT 1199283171.887953002212558175021 + 5 * 3600
UNION ALL SELECT 1199283171.887953002212558175021 + 6 * 3600
UNION ALL SELECT 1199283171.887953002212558175021 + 7 * 3600
UNION ALL SELECT 1199283171.887953002212558175021 + 8 * 3600
UNION ALL SELECT 1199283171.887953002212558175021 + 9 * 3600
)
SELECT
secs
, '1980-01-01'::TIMESTAMP + (secs::VARCHAR(32)||' SECOND')::INTERVAL AS add_interval
, '1980-01-01'::TIMESTAMP + (secs/86400) AS add_day_fraction
, TIMESTAMPADD(MICROSECOND,(1000000*secs)::INT, '1980-01-01'::TIMESTAMP) AS using_function
FROM input;
secs | add_interval | add_day_fraction | using_function
----------------------------------+----------------------------+----------------------------+----------------------------
1199283171.887953002212558175021 | 2018-01-01 14:12:51.887953 | 2018-01-01 14:12:51.887953 | 2018-01-01 14:12:51.887953
1199286771.887953002212558175021 | 2018-01-01 15:12:51.887953 | 2018-01-01 15:12:51.887953 | 2018-01-01 15:12:51.887953
1199290371.887953002212558175021 | 2018-01-01 16:12:51.887953 | 2018-01-01 16:12:51.887953 | 2018-01-01 16:12:51.887953
1199293971.887953002212558175021 | 2018-01-01 17:12:51.887953 | 2018-01-01 17:12:51.887953 | 2018-01-01 17:12:51.887953
1199297571.887953002212558175021 | 2018-01-01 18:12:51.887953 | 2018-01-01 18:12:51.887953 | 2018-01-01 18:12:51.887953
1199301171.887953002212558175021 | 2018-01-01 19:12:51.887953 | 2018-01-01 19:12:51.887953 | 2018-01-01 19:12:51.887953
1199304771.887953002212558175021 | 2018-01-01 20:12:51.887953 | 2018-01-01 20:12:51.887953 | 2018-01-01 20:12:51.887953
1199308371.887953002212558175021 | 2018-01-01 21:12:51.887953 | 2018-01-01 21:12:51.887953 | 2018-01-01 21:12:51.887953
1199311971.887953002212558175021 | 2018-01-01 22:12:51.887953 | 2018-01-01 22:12:51.887953 | 2018-01-01 22:12:51.887953
1199315571.887953002212558175021 | 2018-01-01 23:12:51.887953 | 2018-01-01 23:12:51.887953 | 2018-01-01 23:12:51.887953