PostgreSQL中具有不同时区的时间戳

Question

我的任务是在3、5、7天后归档数据。

我使用PostgreSQL。一些表格有带时区的时间戳条目，另一些没有。当转换为日期时，我会得到相同时间点不同值。如何规范化以获得一致的数据集？

日期格式为“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 := totalCount || '--' || 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;

- rachek

Answer 1

试试这个：

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

.. UTC代表“协调世界时”。您可以使用任何其他时区名称或缩写-阅读文档。有关接受的时区缩写列表：

SELECT * FROM pg_timezone_abbrevs;  

请按照以下方式编写查询：

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

- Erwin Brandstetter