-2

How do you remove the time component from a timestamp for instance:

2014-01-01 12:00:01

To become

2014-01-01 00:00:00
Kermit
  • 33,827
  • 13
  • 85
  • 121
c 2
  • 1,127
  • 3
  • 13
  • 21
  • 1
    your title is somewhat confusing. do you actually want to remove the timestamp piece entirely so that you'd have a simple date - 2014-01-01' or do you actually want to reset it to 00:00:00 – rhealitycheck Jul 01 '14 at 18:46
  • just reset to 00:00:00. – c 2 Jul 01 '14 at 18:47
  • I would highly encourage going through the documentation before asking a question. See [this](http://whathaveyoutried.com) – Kermit Jul 02 '14 at 02:29
  • I did try the doc but I obviously missed it. Googling didn't help much either and that's why the ask. – c 2 Jul 02 '14 at 02:53

2 Answers2

4

It seems like you're actually asking how to remove the time component from a timestamp type.

Let's set up some test data:

CREATE TABLE public.test (
    time_stamp timestamp
);

INSERT INTO public.test (time_stamp) VALUES (SYSDATE());
INSERT INTO public.test (time_stamp) VALUES (SYSDATE());
INSERT INTO public.test (time_stamp) VALUES (SYSDATE());
COMMIT;

Before the transformation, the data looks like:

         time_stamp
----------------------------
 2014-07-01 21:37:11.454081
 2014-07-01 21:37:15.521172
 2014-07-01 21:37:18.048398

We'll use the TRUNC function to truncate the value:

SELECT TRUNC(time_stamp, 'dd') FROM public.test;

This produces:

        TRUNC
---------------------
 2014-07-01 00:00:00
 2014-07-01 00:00:00
 2014-07-01 00:00:00

If you prefer to write it out:

SELECT CAST(CAST(time_stamp AS date) AS timestamp) FROM public.test;

Or

SELECT time_stamp::date::timestamp FROM public.test;
Kermit
  • 33,827
  • 13
  • 85
  • 121
-2

If you know the actual difference that you want to reset you can use the timestampadd. For your example, you would need to do this twice:

select timestampadd(ss, -1, (timestampadd(hh, -12, '2014-01-01 12:00:01')))

this is the link to the vertica documentation on how to use timestampadd: https://my.vertica.com/docs/5.0/HTML/Master/16090.htm

rhealitycheck
  • 650
  • 3
  • 8
  • 19
  • So if there are hr, min and sec, does it mean I have to do it three times? Doesn't sound very efficient to me... – c 2 Jul 01 '14 at 19:27