Select timestamp with time zone, but ignore seconds

Question

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?

Cabotage

Answer 1

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') ...

This formats the timestamptz value according to your current timezone setting. See:

• Ignoring time zones for Postgres timestamps

• Discard millisecond part from timestamp

Gothurd

Answer 2

With timezone:

SELECT date_trunc('minute', TABLE_NAME.COLUMN AT TIME ZONE '+03') FROM TABLE_NAME

Curiel

Answer 3

Well, there are many ways to handle this, but the efficient way is to use date_trunc, as mentioned in the privous answer.

An alternative pproach is to use to_char function.

Example

SELECT TO_CHAR(timestamp_column, 'YYYY-MM-DD HH24:MI') AS formatted_ts
FROM table_name;

Grocer