How do you remove time component from a timestamp?

Question

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

Answer 1

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;