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.

Er ... `SELECT fieldname FROM table`. More details please, this doesn't make much sense as written. PostgreSQL version? Client program/driver you're using to access PostgreSQL? Code that shows the problem you're having? — Craig Ringer, Jun 28 '13 at 10:53
try some of these: http://www.postgresql.org/docs/9.1/static/functions-datetime.html — davek, Jun 28 '13 at 10:54

davek · Accepted Answer · 2013-06-28T11:06:51.603

23

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

See the list here

e.g.

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

For formatting you can use these:

e.g.

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

edited Jun 28 '13 at 11:06

answered Jun 28 '13 at 10:55

davek

score 23 · Answer 2 · edited May 23 '17 at 12:18

23

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());

edited May 23 '17 at 12:18

Community

answered Jun 28 '13 at 18:37

score 5 · Answer 3 · answered Jun 09 '16 at 13:46

5

This should be enough:

select now()::date, now()::time
    , pg_typeof(now()), pg_typeof(now()::date), pg_typeof(now()::time)

answered Jun 09 '16 at 13:46

albfan

3 Answers3