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.
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') ...
To get the date from a timestamp (or timestamptz) a simple cast is fastest:
SELECT now()::date;
For timestamptz input, you get the date according to thetimezone setting of the current session. Transpose to a given time zone with the AT ZIME ZONE construct the time zone differs. See:
For text output in a custom format, use to_char() like davek provided.
To truncate (round down) the value of a timestamp to a given unit of time, use date_trunc():
SELECT date_trunc('day', now());
This should be enough:
select now()::date, now()::time
, pg_typeof(now()), pg_typeof(now()::date), pg_typeof(now()::time)
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?