Skip to content

Edoardo Vignati

– Always looking for something awesome –

Menu
  • About me
  • Studies
  • Publications
  • Skills
  • Portfolio
  • Projects
  • Career
  • Blog
Menu

PostgreSQL date/time tips

Posted on 28/12/201801/12/2020 by Edoardo

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

Buy Me A Coffee


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

Leave a Reply Cancel 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.

  1. Edoardo on [SOLVED] XMLHttpRequest error in Flutter web is a CORS error06/01/2023
  2. Fabi on [SOLVED] XMLHttpRequest error in Flutter web is a CORS error20/12/2022
  3. Edoardo on How to install hplip on Ubuntu28/11/2022
  4. Alex K on How to install hplip on Ubuntu28/11/2022

© 2023 Edoardo Vignati | Powered by Minimalist Blog WordPress Theme
Manage Cookie Consent
To provide the best experiences, we use technologies like cookies to store and/or access device information. Consenting to these technologies will allow us to process data such as browsing behavior or unique IDs on this site. Not consenting or withdrawing consent, may adversely affect certain features and functions.
Functional Always active
The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
Preferences
The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
Statistics
The technical storage or access that is used exclusively for statistical purposes. The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
Marketing
The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
Manage options Manage services Manage vendors Read more about these purposes
View preferences
{title} {title} {title}