postgresql - 如何在 Postgres/Postgis 中转换类型(并使用 sequelizejs)

Question

我在 postgis 中有矢量图 block 。没有类型转换类型的经验。 vector-tile-spec 支持以下类型:string、float、double、int64、uint64、sint64、bool。这意味着除 Varchar、Text、CharArray、Float4、Float8、Int2、Int4、Int8、Bool 之外的所有 PostgreSQL 类型都必须转换为其中之一。

我的表中有两个字段是日期，即“带时区的时间戳”:createdAt 和 updatedAt。我想存储在受支持的类型中，那么我该怎么做呢？

我目前:

1. 使用 sequelize 制作模式(a.o. 定义 DATE 类型)
2. 使用 ogr2ogr 批量导入(Postgis 在其中创建默认值日期)
3. 然后是带有 sequelize 的 CRUD。

但是所有 3 个(或至少 2 个和 3 个)最终都需要成为受支持的类型。

• 我最适合将 DATE 转换为哪种类型？
• 我该怎么做(使用 sequelize hooks？使用 Postgres？)？

Answer 1

我会将您的日期转换为 INTEGER 或 BIGINT，具体取决于您需要使用的日期范围，并且只计算 Unix Time 的天数.

假设您只是转换为 INTEGER，您可以定义以下函数以将 dates 转换为从 epoch date 算起的天数(1970-01-01 00:00:00):

CREATE FUNCTION days_from_reference_date(d date)
RETURNS INTEGER
AS
$$
    SELECT
        CAST(EXTRACT(epoch from d) / (60 * 60 * 24 /* seconds in a day */) AS INTEGER) ;
$$
LANGUAGE SQL
STRICT
IMMUTABLE ;

你可以在这样的场景中使用它:

SELECT
    days_from_reference_date(d) AS transformed_date
FROM
    dates
ORDER BY
    transformed_date ;

反向转换可以通过以下方式完成:

CREATE FUNCTION date_from_days_from_reference_date(days integer)
RETURNS date
AS
$$
    SELECT CAST(to_timestamp(cast(days as bigint) * 60 * 60 * 24) AS date);
$$
LANGUAGE SQL
STRICT
IMMUTABLE ;

您可以在 dbfiddle here 查看一些示例

---

引用资料:

• Postgresql Date/Time Functions and Operators .寻找 to_timestamp 和 extract(epoch from ...)。

---

警告:日期在公元前一天偏移；如果您需要它们。它可能可以通过在反向转换功能中检查它们来解决。