sqlverticavsql

How do you remove time component from a timestamp?

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
Solution

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