Extract Date from timestamp

BhargavBhargav Vertica Customer
edited September 2019 in General Discussion

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


  • Options
    marcothesanemarcothesane - Select Field - Administrator

    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 ...

  • Options
    BhargavBhargav Vertica Customer
    edited September 2019

    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

  • Options
    mflowermflower Vertica Employee Employee

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



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

  • Options
    marcothesanemarcothesane - Select Field - Administrator

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

        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'
        , 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
  • Options
    BhargavBhargav Vertica Customer

    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

  • Options
    Vertica_CurtisVertica_Curtis Employee
    edited September 2019

    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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file