Extract Date from timestamp

Bhargav

HI,
I have a selected data of Logger and in column Event Time date is in TIMESTAMPTZ "2019-08-09 00:01:00" format. I have to extract only date from this particular EventTime column and store it into another table of column for Machine Learning. is it possible?
Using Vertica 9.2.1 Enterprise Mode.

I tried to extract date from this column but not working. Help me out with this?

Bhargav Vyas

marcothesane

If the event timestamp is event_ts, then just go: event_ts::DATE.
:: is the casting operator. And hard-casting dates truncates.
That's all ...

Bhargav

Hi Marcothesane,
Thanks for response !!!
But how can i extract the date from this particular format "2019-08-09 00:01:00". I have 30,000 to 50,000 rows in this column. can you please explain?

Bhargav Vyas

mflower

I agree with MarcoTheSane:
SELECT '2019-08-09 00:01:00'::date AS marco;

marco

2019-08-09

If this is not what you are looking for, please can you provide a sample of expected output.

marcothesane

I understood you want this - don't you? (sorry I can't get it to display monospace characters ...)

    WITH
    input (ts) AS (
              SELECT TIMESTAMP '2019-08-09 00:01:00'
    UNION ALL SELECT TIMESTAMP '2019-05-04 23:45:42'
    UNION ALL SELECT TIMESTAMP '2019-05-04 23:45:42'
    UNION ALL SELECT TIMESTAMP '2019-06-05 23:46:42'
    UNION ALL SELECT TIMESTAMP '2019-07-06 23:47:42'
    UNION ALL SELECT TIMESTAMP '2019-08-07 23:48:42'
    )
    SELECT
      ts
    , ts::DATE AS made_to_date
    FROM input;
    -- out          ts          | made_to_date 
    -- out ---------------------+--------------
    -- out  2019-08-09 00:01:00 | 2019-08-09
    -- out  2019-05-04 23:45:42 | 2019-05-04
    -- out  2019-05-04 23:45:42 | 2019-05-04
    -- out  2019-06-05 23:46:42 | 2019-06-05
    -- out  2019-07-06 23:47:42 | 2019-07-06
    -- out  2019-08-07 23:48:42 | 2019-08-07
    -- out (6 rows)
    -- out 
    -- out Time: First fetch (6 rows): 49.068 ms. All rows formatted: 49.162 ms

Bhargav

Hi,
How to resolve this error ?
"The response_column is included in the predictor_columns. You should exclude it to have a meaningful model".
Bhargav Vyas

Vertica_Curtis

Can you send the syntax of what you're doing? It sounds like you're including this data into a machine learning model. The response column is the dependent variable. You can't include that in your set of independent variables. In other words, I can't predict sunny weather by including "sunny" in the set of variables I use to predict the weather. Because that wouldn't make a very good predictive model.

In other words, if your dependent variable is "x", and your dependent variables are "w, y, z", don't pass "w, x, y, z" into your set of independent variables - take "x" out.