Get ADD_MONTH() function to not truncate time?

Moderator: NorbertKrupa

Post Reply
User avatar
piglet
Beginner
Beginner
Posts: 45
Joined: Tue Feb 07, 2012 4:04 pm

Get ADD_MONTH() function to not truncate time?

Post by piglet » Thu Jun 18, 2015 7:44 pm

Howdy!

How do I get the add_months() function to not truncate the time on a timestamp?

Code: Select all

dbadmin=> select now(), add_months(now(), 1)::timestamp;
              now              |     add_months      
-------------------------------+---------------------
 2015-06-18 14:35:51.644416-04 | 2015-07-18 00:00:00
(1 row)
I want to keep the time!

Thank you!

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1815
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Get ADD_MONTH() function to not truncate time?

Post by JimKnicely » Thu Jun 18, 2015 8:20 pm

Piglet,

That's an interesting question. To be honest, I thought the function did keep the time. Anyway, if you describe the function in vsql, you will see that the data type returned is DATE which in Vertica does not include time...

Code: Select all

dbadmin=> \df add_months 
                         List of functions
 procedure_name | procedure_return_type | procedure_argument_types 
----------------+-----------------------+--------------------------
 add_months     | Date                  | Timestamp, Integer
(1 row)
Maybe you can try appending the time and converting the string to a timestamp? Like this?

Code: Select all

dbadmin=> SELECT now(),
dbadmin->        add_months(now(), 1),
dbadmin->        TO_CHAR(add_months(now(),1) || TO_CHAR(now(), ' HH24:MI:SS.FFTZH'))::TIMESTAMP WITH TIMEZONE with_time;
              now              | add_months |           with_time           
-------------------------------+------------+-------------------------------
 2015-06-18 14:35:51.644416-04 | 2015-07-18 | 2015-07-18 14:35:51.644416-04
(1 row)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica SQL Functions”