Hello I am using WITH A AS... , B as .... commands. However, in C I needed to take first value from a column in B table. Tried to use ```select(substr(select Table1 from B,0,1)
However I had an error. Can you help, how can I extract first value in the temporary tables?
I.E. data is
CREATE temp TABLE XXX (DATE TEXT, PRICE INTEGER);
INSERT INTO XXX VALUES
('2019-04-27 01:00', 1), ('2019-04-27 02:30', 3), ('2019-04-27 18:00',2),
('2019-04-28 17:00', 2), ('2019-04-28 21:00', 5),
('2019-04-29 17:00',50), ('2019-04-29 21:00',10),
('2019-04-30 17:00',10), ('2019-04-30 21:00',20),
('2019-05-01 17:00',40), ('2019-05-01 21:00',10),
('2019-05-02 17:00',10), ('2019-05-02 21:00', 6);
In A table I get 3 the most recents dates. In B column I calculated difference in days between A dates and Today. However data also includes hours/minutes. By using substring in Table C I wanted to avoid minutes/hours in Table B.
Looks like you are making your first steps into a relational database - welcome to the fun!
I changed the script to hard-cast the TRUNC() expression to a DATE type explicitly.
Alternatively, you can also use the function TIMESTAMPDIFF() (Which is DATEDIFF() in SQL Server, for example).
We have - not only in Vertica, but also in all DBMSs, data types and functions for those data types that make your life much , much easier.
In the example below, I use the right type for a literal like '2019-04-27 01:00' - which is a timestamp without fractions of seconds - a TIMESTAMP(0) .
You can get the date part of a timestamp with the function TRUNC(the_timestamp) which I use below.
And in a big number of RDBMS-s , you can subtract whole dates and get integers as difference - among which Vertica - which I do.
Selecting the 3 most recent dates is selecting the date, order by it descending, and limiting the result to 3 rows.
See what I do here below ...
CREATE LOCAL TEMPORARY TABLE xxx(dt, price)
ON COMMIT PRESERVE ROWS AS (
SELECT TIMESTAMP '2019-04-27 01:00', 1
UNION ALL SELECT TIMESTAMP '2019-04-27 02:30', 3
UNION ALL SELECT TIMESTAMP '2019-04-27 18:00', 2
UNION ALL SELECT TIMESTAMP '2019-04-28 17:00', 2
UNION ALL SELECT TIMESTAMP '2019-04-28 21:00', 5
UNION ALL SELECT TIMESTAMP '2019-04-29 17:00',50
UNION ALL SELECT TIMESTAMP '2019-04-29 21:00',10
UNION ALL SELECT TIMESTAMP '2019-04-30 17:00',10
UNION ALL SELECT TIMESTAMP '2019-04-30 21:00',20
UNION ALL SELECT TIMESTAMP '2019-05-01 17:00',40
UNION ALL SELECT TIMESTAMP '2019-05-01 21:00',10
UNION ALL SELECT TIMESTAMP '2019-05-02 17:00',10
UNION ALL SELECT TIMESTAMP '2019-05-02 21:00', 6
)
;
-- GET THE 3 MOST RECENT DATES ...
SELECT
dt
FROM xxx ORDER BY dt DESC
LIMIT 3
;
-- out dt
-- out ---------------------
-- out 2019-05-02 21:00:00
-- out 2019-05-02 17:00:00
-- out 2019-05-01 21:00:00
-- CALCULATE DIFFERENCE IN DAYS BETWEEN THE DATE AND TODAY ..
SELECT
*
, CURRENT_DATE - TRUNC(dt)::DATE AS daydiff_to_today
, TIMESTAMPDIFF(DAY,TRUNC(dt), CURRENT_DATE) AS daydiff_timestampdiff
FROM xxx
ORDER BY 1;
-- out dt | price | daydiff_to_today | daydiff_timestampdiff
-- out ---------------------+-------+------------------+-----------------------
-- out 2019-04-27 01:00:00 | 1 | 366 | 366
-- out 2019-04-27 02:30:00 | 3 | 366 | 366
-- out 2019-04-27 18:00:00 | 2 | 366 | 366
-- out 2019-04-28 17:00:00 | 2 | 365 | 365
-- out 2019-04-28 21:00:00 | 5 | 365 | 365
-- out 2019-04-29 17:00:00 | 50 | 364 | 364
-- out 2019-04-29 21:00:00 | 10 | 364 | 364
-- out 2019-04-30 17:00:00 | 10 | 363 | 363
-- out 2019-04-30 21:00:00 | 20 | 363 | 363
-- out 2019-05-01 17:00:00 | 40 | 362 | 362
-- out 2019-05-01 21:00:00 | 10 | 362 | 362
-- out 2019-05-02 17:00:00 | 10 | 361 | 361
-- out 2019-05-02 21:00:00 | 6 | 361 | 361