2.5 Date & Time

SET TIMEZONE TO 'PST';
SET TIMEZONE TO 'CETDST';

SELECT now()::date;
SELECT now()::time;
SELECT now()::timetz;
SELECT now()::abstime;		2003-12-02 02:51:20-08

Postgres built-in date and time calculation functions

SELECT now()::date + INTERVAL '1 DAY';
SELECT now()::date + INTERVAL '3 DAYS';
SELECT now()::time + INTERVAL '9 HOURS';

Geek Problem:
is of type abstime but expression is of type time without time zone

Time Zones

Postgres obtains time zone support from the underlying operating system. All dates and times are stored internally in Universal Coordinated Time (UTC), alternately known as Greenwich Mean Time (GMT). Times are converted to local time on the database server before being sent to the client frontend, hence by default are in the server time zone.

There are several ways to affect the time zone behavior:

• The TZ environment variable used by the backend directly on postmaster startup as the default time zone.

• The PGTZ environment variable set at the client used by libpq to send time zone information to the backend upon connection.

• The SQL command SET TIME ZONE sets the time zone for the session.