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;