The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Extract Date from timestamp

edited September 11 in General Discussion

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

Comments

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

  • edited September 11

    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

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

  • 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
    
  • 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_CurtisVertica_Curtis Employee
    edited September 12

    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
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.