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

Date/Time Functions are working wrong

Vertica version 9.2 Date/Time Functions are working wrong
when we provide column_name which contains many rows with "DateTime" (Column Sample: 2019-05-11 23:45:29), the hour value in output we receive is completely wrong when compare to actual hour from actual table (column_name)
For Example: ** Column Sample: 2019-05-11 23:45:29`
**Query:
--SELECT HOUR ( "column_name");--
Output: 12(Instead of 23. I got 12).
(or)
--SELECT TIMESTAMP_TRUNC("column_name",'HH'),"column_name",TRUNC("column_name") FROM Table_Name;--
(or)
-- SELECT TO_CHAR ("column_name", 'HH24:MI:SS') AS my_date FROM Table_Name; --
But Hard-code is working correctly
For Example: --SELECT HOUR (TIMESTAMP '2019-05-11 23:45:29');--
--SELECT HOUR ( '2019-05-11 23:45:29');--
I don't know y these functions are wrongly calculating. Can anyone Help to solve this issue?

Comments

  • Bryan_HBryan_H Employee

    Instead of a column sample, could you post a test case that reproduces the issue? This simple case works for me, so how is your table and query different than the following:

    dbadmin=> CREATE TABLE testing(c1 TIMESTAMP);
    CREATE TABLE
    dbadmin=> INSERT INTO testing VALUES ('2019-05-11 23:45:29');
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> SELECT HOUR(c1) from testing;
     HOUR
    ------
       23
    (1 row)
    

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.