I know, editing date/time fileds in a database is not easy and sometimes we don’t use good techniques to do it because of the difficulty. In some of my projects I had to manage timestamp, so, I would like to share some useful tips.
Split a timestamp into two elements:
SELECT d[1] AS date, d[2] AS time FROM regexp_split_to_array('2018-09-14 10:00:10',' ') AS d;
Get a string from edited date layout:
SELECT TO_CHAR(now() :: DATE, 'dd-mm-yyyy');
Concatenate date and time to timestamp:
-- Before: create a new column ALTER TABLE mytable ADD COLUMN mytimestamp; -- Then: fill the column UPDATE mytable SET mytimestamp = TO_TIMESTAMP(date || ' ' || time, 'yyyy-mm-dd hh24:mi:ss')::timestamp;
Difference in seconds between timetamps:
SELECT (EXTRACT(EPOCH FROM next_timestamp) - EXTRACT(EPOCH FROM previous_timestamp)) AS difference_in_sec
Add hours to a timestamp:
-- Add hours to a timestamp SELECT date_field + interval '_numberOfHours_h' * start_time -- Examples SELECT now() + interval '1h' * 0 as now SELECT now() + interval '1h' * 1 as now_plus_1hour SELECT now() + interval '1h' * 2 as now_plus_2hour SELECT now() + interval '2h' * 1 as now_plus_2hour