Getting date with timezone offset

If 'America/Los_Angeles' is your current time zone (timezone setting of the current session) then just tell Postgres the time zone of the timestamp.

SELECT ('2014-01-01 02:00:00'::timestamp AT TIME ZONE 'UTC')::date;

The cast to date is based on the current time zone and works automatically as desired.

If the current time zone can be something else, you need to be explicit like @Clodoaldo demonstrates.

The proper solution would be to store a timestamp with time zone (timestamptz) to begin with, then you can just cast to date:

SELECT '2014-01-01 02:00:00+0'::timestamptz::date;

About timestamps in Postgres:

• Ignoring time zones altogether in Rails and PostgreSQL

Recent related answer with more details (also dealing with indexes):

• Optimize/Index Timezone Query

If it is timestamp without time zone you need first to tell it to use the UTC time zone and then convert to another time zone:

SELECT '1-1-2014 02:00:00'::timestamp at time zone 'UTC' at time zone 'America/Los_Angeles';
      timezone       
---------------------
 2013-12-31 18:00:00