How to get the date and time from timestamp in PostgreSQL select query?

Question

How to get the date and time only up to minutes, not seconds, from timestamp in PostgreSQL. I need date as well as time.

For example:

2000-12-16 12:21:13-05 

From this I need

2000-12-16 12:21 (no seconds and milliseconds only date and time in hours and minutes)

From a timestamp with time zone field, say update_time, how do I get date as well as time like above using PostgreSQL select query.

Please help me.

Answer 1

To get the date from a timestamp (or timestamptz) a simple cast is fastest:

SELECT now()::date

You get the date according to your local time zone either way.

If you want text in a certain format, go with to_char() like @davek provided.

If you want to truncate (round down) the value of a timestamp to a unit of time, use date_trunc():

SELECT date_trunc('minute', now());