Timestamps with varying time zones in PostgreSQL

My task is to archive data after 3, 5, 7 days.

I use PostgreSQL. Some tables have timestamp entries with time zone, others without. When casting to date, I get different values for identical points in time. How can I normalize those to get consistent datasets in my archives?

Dates are like "2011-08-01 17:03:19+05:30."

CREATE OR REPLACE FUNCTION archive(tablename text, fieldname text, days integer, archivepath text)
  RETURNS text AS
$BODY$
declare
format_date text;
fileName text;
totalCount integer;
fileNamewithCount text;

stmt text;
BEGIN
format_date:=to_char(localTIMESTAMP,'YYYYmmddhhmmss');

stmt := 'SELECT COUNT(*) FROM '|| tableName ||' WHERE DATE('||fieldName||') < DATE(CURRENT_DATE -'||days ||')';

EXECUTE stmt INTO totalCount;

IF totalCount > 0 THEN
--raise notice 'format_date : %',format_date;
fileName := 'D:/' || '/' || archivepath || '/' || tableName || '_' || format_date ||'.csv';


EXECUTE 'COPY (SELECT * FROM ' || quote_ident(tableName) || ' where date(' || quote_ident(fieldName)||') < date((current_date - integer '''|| days ||''')) limit 100000) TO '''|| fileName ||''' WITH CSV HEADER'; 

EXECUTE 'DELETE FROM ' || quote_ident(tableName) ||' where date(' || quote_ident(fieldName) || ') < date((current_date - integer '''||days ||'''))';

END IF;
fileNamewithCount := totalCoun开发者_开发问答t || '--' || fileName ;

RETURN  fileNamewithCount;

EXCEPTION
        WHEN OTHERS THEN RAISE EXCEPTION 'ERROR: % : %.',SQLSTATE,SQLERRM;

END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION archive(text, text, integer, text) OWNER TO postgres;

Try this:

SELECT TIMESTAMP '2001-02-16 20:38:40+1' AT TIME ZONE 'UTC';

.. where UTC stands for "Coordinated Universal Time". You can use any other time zone name or abbreviation - read the docs. For a list of accepted timezone abbreviations:

SELECT * FROM pg_timezone_abbrevs;  

Write your query like this:

SELECT * FROM my_tbl WHERE (my_ts_fld AT TIME ZONE 'UTC')::date = $my_date;