PostgreSQL date/time tips

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

This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.