Postgresql select timestamp with time zone, but ignore seconds

postgresql timestamp-with-timezone
Ben Walker picture Ben Walker · Nov 3, 2015 · Viewed 7.8k times · Source

I'm selecting a timestamptz from a posgresql database. I want my select to return only the date, hours and minutes. No seconds. Can I set the date format in my psql select to accommodate this?

Answer

Erwin Brandstetter picture Erwin Brandstetter · Nov 3, 2015

You can use date_trunc() to truncate seconds and still return a timestamptz:

SELECT date_trunc('minute', ts_col) ...

Or you can use to_char() to return a formatted timestamp as text any way you like:

SELECT to_char(ts_col, 'YYYY-MM-DD HH24:MI') ...

Note that this will format the timestamptz according to your current time zone setting. Details:

Related questions

How can i insert timestamp with timezone in postgresql with prepared statement?

I am trying to insert to a timestamp with timezone field of my DB a string which includes date, time and timezone using prepared statement. The problem is that Timestamp.valueof function does not take into consideration the time zone …

Read More
Date and time in UTC - how to store them in postgres?

I am getting my data: date and time in UTC, in a csv file format in separate columns. Since I will need to convert this zone to date and time of the place where I live, currently in summer to …

Read More
Converting Between Timezones in Postgres

I am trying to understand the timestamps and timezones in Postgre. I think I got it, until I red this article. Focus on the "Converting Between Timezones" part. It has two examples. (Consider the default timezone configuration to be UTC.) …

Read More