user2515189 user2515189 - 5 months ago 19
SQL Question

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

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

There are plenty of date-time functions available with postgresql:

See the list here

http://www.postgresql.org/docs/9.1/static/functions-datetime.html

e.g.

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 16

For formatting you can use these:

http://www.postgresql.org/docs/9.1/static/functions-formatting.html

e.g.

select to_char(current_timestamp, 'YYYY-MM-DD HH24:MI') ...
Comments