pgsql 第八节 数据类型

最新推荐文章于 2024-05-13 14:03:56 发布
转载 最新推荐文章于 2024-05-13 14:03:56 发布 · 2.1k 阅读 · 0
文章标签：

#pgsql

本文详细介绍了 PostgreSQL 中的各种数据类型，包括数值类型、货币类型、字符类型等，并深入探讨了每种类型的特性和应用场景。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

Chapter 8. 数据类型

Table of Contents 8.1.  数值类型
8.1.1.  整数类型 8.1.2.  任意精度数值 8.1.3.  浮点数类型 8.1.4.  Serial（序号）类型
8.2.  货币类型 8.3.  字符类型 8.4.  二进制数据类型 8.5.  日期/时间类型
8.5.1.  日期/时间输入 8.5.2.  日期/时间输出 8.5.3.  时区 8.5.4.  内部
8.6.  布尔类型 8.7.  几何类型
8.7.1.  Point（点） 8.7.2.  线段 8.7.3.  Box（方） 8.7.4.  Path（路径） 8.7.5.  Polygon（多边形） 8.7.6.  Circle（圆）
8.8.  网络地址数据类型
8.8.1.  inet 8.8.2.  cidr 8.8.3.  inet 与 cidr 对比 8.8.4.  macaddr
8.9.  位串类型 8.10.  数组
8.10.1.  数组类型的声明 8.10.2.  数组值输入 8.10.3.  访问数组 8.10.4.  修改数组 8.10.5.  在数组中检索 8.10.6.  数组输入和输出语法
8.11.  复合类型
8.11.1.  声明复合类型 8.11.2.  复合类型值输入 8.11.3.  访问复合类型 8.11.4.  修改复合类型 8.11.5.  复合类型输入和输出语法
8.12.  对象标识符类型 8.13.  伪类型

PostgreSQL 有着丰富的数据类型可用。 用户可以使用 CREATE TYPE 命令为 PostgreSQL 增加新的数据类型。

Table 8-1 显示了所有内置的普通数据类型。 在"别名"列里列出的大多数可选名字都是因历史原因 PostgreSQL 在内部使用的名字。 另外，还有一些内部使用的或者废弃的类型也可以用，但没有在这里列出。

Table 8-1. 数据类型

名字别名描述
bigintint8有符号 8 字节整数
bigserialserial8自增八字节整数
bit [ (n) ] 定长位串
bit varying [ (n) ]varbit变长位串
booleanbool逻辑布尔量 （真/假）
box 平面中的长方形
bytea 二进制数据（"字节数组"）
character varying [ (n) ]varchar [ (n) ]变长字符串
character [ (n) ]char [ (n) ]定长字符串
cidr IPv4 或者 IPv6 网络地址
circle 平面中的圆
date 日历日期（年，月，日）
double precisionfloat8双精度浮点数字
inet IPv4 或者 IPv6 网络地址
integerint，int4四字节长有符号整数
interval [ (p) ] 时间间隔
line 平面中的无限长直线
lseg 平面中的线段
macaddr MAC 地址
money 货币金额
numeric [ (p, s) ]decimal [ (p, s) ]可选精度的准确数字
path 平面中的几何路径
point 平面中的点
polygon 平面中的封闭几何路径
realfloat4单精度浮点数
smallintint2有符号两字节整数
serialserial4自增四字节整数
text 变长字符串
time [ (p) ] [ without time zone ] 一天里的时间
time [ (p) ] with time zonetimetz一天里的时间，包括时区
timestamp [ (p) ] [ without time zone ] 日期和时间
timestamp [ (p) ] with time zonetimestamptz日期和时间

兼容性: 下列类型（或者那样拼写的）是SQL声明的： bit，bit varying，boolean， char，character，character varying，varchar，date， double precision，integer， interval，numeric，decimal， real，smallint，time （包括有时区和无时区的）， timestamp （包括有时区和无时区的）。

每种数据类型都有一个由其输入和输出函数决定的外部表现形式。 许多内建的类型有明显的格式。不过，许多类型要么是 PostgreSQL 所特有的，比如几何路径，要么可能是有几种不同的格式，比如日期和时间类型。 有些输入和输出函数是不可逆的。也就是说，输出函数的输出结果和原始的输入比较的时候可能丢失精度。

8.1. 数值类型

数值类型由2、4或8字节的整数以及4或8字节的浮点数和可选精度小数组成。 Table 8-2 列出了所有可用类型。

Table 8-2. 数值类型

名字存储空间描述范围
smallint2 字节小范围整数-32768 到 +32767
integer4 字节常用的整数-2147483648 到 +2147483647
bigint8 字节大范围的整数-9223372036854775808 到 9223372036854775807
decimal变长用户声明精度，精确无限制
numeric变长用户声明精度，精确无限制
real4 字节变精度，不精确6 位十进制数字精度
double precision8 字节变精度，不精确15 位十进制数字精度
serial4 字节自增整数1 到 +2147483647
bigserial8 字节大范围的自增整数1 到 9223372036854775807

数值类型常量的语法在 Section 4.1.2 里描述。 数值类型对应有一套完整的数学操作符和函数。相关信息请参考 Chapter 9。 下面的几节详细描述这些类型。

8.1.1. 整数类型

类型 smallint，integer，和 bigint 存储各种范围的全部是数字的数，也就是没有小数部分的数字。 试图存储超出范围以外的数值将导致一个错误。

常用的类型是 integer，因为它提供了在范围，存储空间， 和性能之间的最佳平衡。一般只有在磁盘空间紧张的时候才使用 smallint。而只有在 integer 的范围不够的时候才使用 bigint，因为前者绝对快得多。

bigint 类型可能不是在所有平台上都运转正确， 因为它依赖编译器对八字节整数的支持。在那些没有这样支持的机器上， bigint 的作用和 integer 一样（但是仍然占据八字节存储）。不过，我们还不知道任何有这样的情况的平台。

SQL只声明了整数类型 integer(或int)和 smallint。类型 bigint，和类型名 int2，int4，和 int8 都是扩展， 也在许多其它 SQL 数据库系统中使用。

8.1.2. 任意精度数值

类型 numeric 可以存储最多1000位精度的数字并且准确地进行计算。 我们特别建议将它用于货币金额和其它要求计算准确的数量。不过，numeric 类型上的算术运算比整数类型或者我们下一节描述的浮点数类型要慢很多。

在随后的内容里，我们使用了下述术语： 一个 numeric 的比例是到小数点右边为止小数部分的位数， numeric 的精度是整个数字里全部数据位的数目，也就是小数点两边的数据数目。 因此数字 23.5141 的精度为6而比例为4。你可以认为整数的比例为零。

numeric 字段的最大精度和最大比例都是可以配置的。要声明一个类型为 numeric 的字段，你可以用下面的语法

NUMERIC(precision, scale)

精度必须为正数，比例可以为零或者正数。 另外，

NUMERIC(precision)

选择了 0 为比例。不带任何精度或者比例声明

NUMERIC

则创建一个可以存储一个直到实现精度上限的任意精度和比例的数值， 一个这样类型的字段将不会把输入数值转化成任何特定的比例， 而带有比例声明的 numeric 字段将把输入值转化为该比例。 （SQL标准要求缺省的比例是 0。也就是转化成整数精度。 我们觉得这样做有点没用。如果你关心移植性，那你最好总是明确声明精度和比例。）

如果一个要存储的数值的比例比字段声明的比例高， 那么系统将尝试圆整（四舍五入）该数值到指定的小数位。 然后，如果小数点左边的数据位数超过了声明的精度减去声明的比例， 那么抛出一个错误。

数值数据值物理上是不带任何前导或者后缀零的形式存储的。 因此，字段上声明的精度和比例都是最大值，而不是固定分配的。 （在这个方面，numeric 类型更类似于 varchar(n)， 而不像 char(n)。） 实际存储是每四个十进制位两个字节，然后在整个数据上加上八个字节的额外开销。

除了普通的数字值之外，numeric 类型允许特殊值 NaN， 表示"不是一个数字"。任何在 NaN 上面的操作都生成另外一个 NaN。 如果在 SQL 命令里把这些值当作一个常量写，你必须在其周围放上单引号，比如 UPDATE table SET x = 'NaN'。在输入时，字串 NaN 当作大小写无关看待。

类型 decimal 和 numeric 是等效的。 两种类型都是SQL标准。

8.1.3. 浮点数类型

数据类型 real 和 double precision 是不准确的，变精度的数字类型。 实际上，这些类型是 IEEE 标准 754 二进制浮点数算术（分别对应单和双精度）的一般实现， 外加下层处理器，操作系统和编译器对它的支持。

不准确意味着一些数值不能准确地转换成内部格式并且是以近似的形式存储的，因此存储然后把数据再打印出来可能显示一些缺失。 处理这些错误以及这些错误是如何在计算中传播的属于数学和计算机科学的一个完整的分支， 我们不会在这里进一步讨论它，这里的讨论仅限于如下几点：

  • 如果你要求准确的计算（比如计算货币金额），应使用 numeric 类型。

  • 如果你想用这些类型做任何重要的复杂计算，尤其是那些你对范围情况（无穷，下溢）严重依赖的事情，那你应该仔细评诂你的实现。

  • 拿两个浮点数值进行相等性比较可能象，也可能不象想像那样运转。

通常，real 类型的范围是至少 -1E+37 到 +1E+37， 精度至少是 6 位小数。double precision 类型通常有 -1E+308 到 +1E+308 的范围，精度是至少 15 位数字。太大或者太小的数值都会导致错误。 如果输入数据太高，那么可能发生园整。太接近零的数字，如果无法与零值的表现形式相区分就会产生下溢错。

除了普通的数字值之外，浮点类型还有几个特殊值：

Infinity
-Infinity
NaN

这些值分别表示 IEEE 754 特殊值 "正无穷大"， "负无穷大"， 以及 "不是一个数字"。（在不遵循 IEEE 754 浮点算术的机器上，这些值的含义可能不是预期的。） 如果在 SQL 命令里把这些数值当作常量写，你必须在它们周围放上单引号， 像这样 UPDATE table SET x = 'Infinity'。 输入时，这些值是以大小写无关的方式识别的。

PostgreSQL 还支持 SQL 标准表示法 float 和 float(p) 用于声明非精确的数值类型。 在这里，p 声明以二进制位表示的最低可接受精度。 在选取 real 类型的时候，PostgreSQL 接受 float(1) 到 float(24)，在选取 double precision 的时候，接受 float(25) 到 float(53)。在允许范围之外的 p 值将导致一个错误。 没有声明精度的 float 将被当作是 double precision。

注意: 在 PostgreSQL 7.4 以前，在 float(p) 里面的精度会被当作是这么多位数的十进制位。到 7.4 已经被修改成与 SQL 标准匹配，标准声明这个精度是以二进制位度量的。假设 real 和 double precision 分别有 24 和 53 个二进制位的位数对 IEEE 标准的浮点实现来说是正确的。 在非 IEEE 平台上，这个数值可能略有偏差，但是为了简化，我们在所有平台上都用了同样的 p 值范围。

8.1.4. Serial（序号）类型

serial 和 bigserial 类型不是真正的类型， 只是为在表中设置唯一标识做的概念上的便利。（类似其它一些数据库中的 AUTO_INCREMENT 属性）。 在目前的实现中，下面一句话：

CREATE TABLE tablename (
	colname SERIAL
);

等价于声明下面几句话：

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename(
	colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
);

因此，我们就创建了一个整数字段并且把它的缺省数值安排为从一个序列发生器取值。 应用了一个 NOT NULL 约束以确保空值不会被明确地插入。 在大多数情况下你可能还希望附加一个 UNIQUE 或者 PRIMARY KEY 约束避免意外地插入重复的数值，但这个不是自动发生的。

注意: 在 PostgreSQL 7.3 以前，serial 隐含 UNIQUE。但现在不再如此。 如果你希望一个序列字段有一个唯一约束或者一个主键，那么你现在必须声明，就像其它数据类型一样。

要使用 serial 字段插入序列的下一个数值到表中， 主要是要注意 serial 应该赋予缺省值。 我们可以通过在 INSERT 语句中把该字段排除在字段列表之外来实现， 也可以通过使用 DEFAULT 关键字来实现。

类型名 serial 和 serial4 是等效的： 两个都创建 integer 字段。类型名 bigserial 和 serial8 也一样，只不过它创建一个 bigint 字段。 如果你预计在表的生存期中使用的标识数目超过 231 个，那么你应该使用 bigserial。

一个 serial 类型创建的序列在所属的字段被删除的时候自动删除，其它情况下是不会被删除的。 （这一点在 PostgreSQL 版本 7.3 之前可不是真的。请注意，这种自动删除的关联在通过重载 7.3 以前的数据库转储的时候可不会自动发生； 那样的转储文件不包含需要建立这种关联关系的信息。） 另外，这样的序列和字段之间的依赖性只在 serial 字段本身上有； 如果任何其它字段引用了序列（可能是手工调用 nextval 函数）， 那么，如果这个序列被删除了，它们就会被破坏。我们认为这样使用 serial 字段是一种不好的形式；如果你想用同一个序列发生器给几个字段喂数据，那么还是把序列发生器作为独立对象创建。



链接：http://www.php100.com/manual/PostgreSQL8/queries.html



z1074907546
关注
【PostgreSQL教程】PostgreSQL 数据类型
No8g攻城狮的博客
07-20 1663
我们将讨论 PostgreSQL 的数据类型，数据类型是我们在创建表的时候为每个字段设置的。PostgreSQL提 供了丰富的数据类型。用户可以使用 CREATE TYPE 命令在数据库中创建新的数据类型。PostgreSQL 的数据类型有很多种，下面我们具体来说明。
PostgreSQL基本数据类型
AntdonYu的博客
06-19 5162
PostgreSQL 提供了丰富的数据类型。用户可以使用 CREATE TYPE 命令在数据库中创建新的数据类型。PostgreSQL 的数据类型被分为四种，分别是基本数据类型、复合数据类型、域和伪类型。 ​ 基本数据类型是数据库内置的数据类型，包括integer、char、varchar等数据类型。表6-1列出了`PostgreSQL提供的所有基本数据类型。复合数据类型是用户自己定义的，使用CREATE TYPE命令就能创建一个复合数据类型。域是一种特殊的基本数据类型，它由基本数据类型加上一个约束条
PostgreSQL不使用扩展，生成随机int8值，生成均匀分布的随机int8值
秋̶᭄ꦿ攻城狮࿆ྂ
06-10 2484
PostgreSQL不使用扩展，生成随机int8值，生成均匀分布的随机int8值,,,,得到一个 [0, 9223372036854775807) 之间的浮点数。函数两次产生两个0到1之间的随机数，并将它们乘以最大值，得到两个潜在的64位无符号整数值。函数和位运算符来生成。函数将它们转换为整数，并分别将它们按位异或，得到一个均匀分布的随机8字节整数。函数计算出一个64位无符号整数的最大值（2的64次方），然后将该值转换为。类型进行值的转换，得到一个随机的8字节整数（int8值）。
postgresql中新增数据类型
小屋
04-20 1656
在ADT目录下面新增一个文件,主要实现下面四个函数 in : 输入函数,把SQL中的输入转化成数据库内部表示 out: 输出函数,把数据库内部表示转化成可视的内容 send recv 相当于二进制的 in ,out   在 include/catalog/pg_proc.h 中 增加那四个函数在 pg_proc的定义,这个pg_proc.h会生成BKI文件   include/c
sql对int类型进行模糊查询
qq_28190039的博客
09-09 3238
使用 cast() 函数 CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型。 语法：CAST (expression AS data_type) 例子： resq_code 为int类型 SELECT * from apirun_result where cast(resq_code as char) like '4%%' ...
postgresql 数据库的自增量从 SERIAL 修改为 SERIAL8
02-04 6455
在postgresql-12数据库中，创建了一个分时数据表，加载的数据超过2亿条。在一次数据对接时，发现自增量主键居然用了 SERIAL，再跑一段时间这张表肯定要爆了~ 现在想调整为 SERIAL8，初始的想法直接修改字段类型： alter table tab_name alter column data_id type SERIAL8; -- 失败！类型SERIAL8不存在！ 但实际上 create table 语句中的字段类型 SERIAL8，并不存在。 SERIAL8 （或者SERIAL）会创
pgsql处理文档类型数据_PostgreSQL数据类型
weixin_39759060的博客
12-20 693
http://blog.youkuaiyun.com/neo_liu0000/article/category/797059第六章 数据类型6.1概述PostgreSQL 提供了丰富的数据类型。用户可以使用 CREATE TYPE 命令在数据库中创建新的数据类型。PostgreSQL 的数据类型被分为四种，分别是基本数据类型、复合数据类型、域和伪类型。基本数据类型是数据库内置的数据类型，包括integer、...
3.pgsql数据类型学习
Lei_Da_Gou的博客
02-11 2098
1.概述 弄好pgsql的环境后，我大概玩了一下，并且数了下里面的数据类型，发现至少有一百多种，WTF？ PostgreSQL 有着丰富的内置数据类型可用。用户还可以使用CREATE TYPE命令增加新的数据类型（摘抄官网）。 PostgreSQL文档地址 https://www.yiibai.com/manual/postgresql/index.html 查看官方文档后发现很...
pgsql12和pgsql9.6（包含对应版本的pggis)
04-28
它支持SQL标准，并提供了一套全面的数据类型和高级查询功能。 “pggis”是PostGIS的缩写，它是一个空间数据库扩展，为PostgreSQL增加了对地理数据的支持。PostGIS使得PostgreSQL能够处理地理对象，如点、线、多边形...
PostgreSQL建表语句 INT, INT2, INT4, INT8 分别对应Java，Go, Python什么数据类型？
猫头虎技术团队：授渔优于赠鱼，兴趣引领智慧，探索之乐尤显珍贵。商务合作+：Libin9iOak ，万粉变现+：优快云WF，猫头虎承诺每年免费为100名C站创作者做账号流量诊断服务！全网搜：猫头虎技术团队，点击文章底部名片或直接私信我一切皆可谈，快找虎哥！
05-13 8817
PostgreSQL是一款功能强大的开源关系型数据库，广泛应用于各类企业级应用。在数据库设计中，数据类型的选择至关重要，尤其是对于跨语言开发的项目，正确理解不同数据库字段类型与编程语言数据类型的对应关系，能极大提高开发效率。本文将详细介绍PostgreSQL中的INT, INT2, INT4, INT8数据类型，并解析它们分别在Java、Go、Python语言中的对应关系，帮助大家轻松掌握这些关键知识点。PostgreSQL类型Java类型Go类型Python类型INTintintint。
PG数据库类型
weixin_48296221的博客
09-06 1885
pg数据库类型
Spring mvc PostgreSQL 插入timestamp和int8
weixin_33922670的博客
04-28 341
timestamp类型的字段用 new Timestamp(System.currentTimeMillis()) 插入 ts的int8用 System.currentTimeMillis()插入
postgresql数据类型
The research on computer technolog
01-10 580
bigint int8 有符号的8字节整数 bigserial serial8 自动增长的8字节整数 bit [ (n) ] 定长位串 bit varying [ (n) ] varbit 变长位串 boolean bool 逻辑布尔值（真/假） box 平面上的普通方框 bytea 二进制数据（“字节数组”） character [ (n) ] char [ (n) ] 定长字符串 ...
pgSQL笔记
weixin_43037331的博客
05-05 548
pgSQL笔记。
PostgreSQL的常见数据类型
香蕉牛奶棒棒槌的博客
03-05 3208
数据类型 1.1 数字类型 PostgreSQL 支持的数字类型有整数类型，用户指定精度类型，浮点型，serial类型。 数字类型列表 smallint，integre，bigint 都是整数类型，存储一定范围的整数，超出范围将会报错。 smallint存储2字节的整数，字段定义时可写成int2。 integer存储4字节整数，支持的数值范围比smallint大，字段定义时可写成int4，是最常...
PostgreSQL数据类型
weixin_33897722的博客
08-31 432
名字别名描述bigintint8有符号 8 字节整数bigserialserial8自增 8 字节整数bit [ (n) ] 定长位串bit varying [ (n) ]varbit变长位串booleanbool逻辑布尔值(真/假)box 平面中的矩形bytea 二进制数据("字节数组")character varying [ (n) ]varchar [ (n) ]变长字...
PostgreSQL - 主键自动增长
秋风未动蝉先觉
03-03 1492
PostgreSQL - 主键自动增长
pgsql中varchar类型转化为int8类型
qq_44372924的博客
10-27 5385
因为数据库中两个表的字段类型不一致，但是需要两表连接查询，并且字段需要进行相等判断，因此需要进行类型转换之后再进行比较。使用CAST函数可以改变字段的类型，然后将varchar类型转化为bigint（int8）类型。
pgsql 查询date类型的数据 时区转换sql at imezone
最新发布
07-26
<think>我们正在处理PostgreSQL中关于date类型字段的查询和时区转换问题。根据引用[1]和引用[2]，我们知道PostgreSQL有日期类型（date）和带时区的时间戳（timestamp with time zone）以及不带时区的时间戳（timestamp without time zone）。但是，date类型本身不包含时区信息。因此，如果我们要对date类型进行时区转换，需要先将date类型转换为带时区的时间戳，然后再进行转换。 步骤： 1. 将date类型转换为timestamp without time zone（因为date没有时间部分，我们可以假设时间为00:00:00）。 2. 将这个timestamp without time zone转换为timestamp with time zone，通过指定这个时间戳的原始时区（即存储时代表的时区，通常为数据库的时区或会话时区）。 3. 使用AT TIME ZONE将时间转换为目标时区的时间戳（不带时区）或者显示为带时区的时间。 注意：在PostgreSQL中，AT TIME ZONE运算符可以将timestamp with time zone转换为目标时区的timestamp without time zone，或者将timestamp without time zone转换为指定时区的timestamp with time zone。 语法： - 如果原始数据是timestamp without time zone，我们可以将其转换为timestamp with time zone： `timestamp_without_tz AT TIME ZONE '原始时区'` -> 得到timestamp with time zone（表示原始时区的时间点） - 然后，再转换为目标时区的时间（不带时区）： `(timestamp_without_tz AT TIME ZONE '原始时区') AT TIME ZONE '目标时区'` -> 得到目标时区的timestamp without time zone 但是，我们的字段是date类型，所以我们需要先将其转换为timestamp without time zone。 假设我们有一个表t，其中有一个date类型的字段d。我们想要将这个日期转换为另一个时区对应的日期（注意：转换时区可能会导致日期变化，比如当原始时区是UTC+0，而目标时区是UTC+8时，原始日期的00:00在目标时区就是08:00，因此日期可能增加一天）。 然而，由于date类型没有时间部分，我们通常需要决定如何解释这个日期。常见做法是假设这个日期的开始时间（即00:00:00）在原始时区。 示例SQL： 假设原始数据存储在UTC时区，我们想转换为时区'America/New_York'。 步骤： 1. 将date字段d转换为timestamp without time zone：`d::timestamp` （这将在该日期的00:00:00） 2. 将这个时间戳视为UTC时区的时间（因为我们的数据是按UTC存储的），然后转换为带时区的时间戳：`(d::timestamp) AT TIME ZONE 'UTC'` 3. 再转换为目标时区的时间戳（不带时区）：`((d::timestamp) AT TIME ZONE 'UTC') AT TIME ZONE 'America/New_York'` 4. 由于我们只关心日期，可以再将其转换为date类型：`(((d::timestamp) AT TIME ZONE 'UTC') AT TIME ZONE 'America/New_York')::date` 但是，注意：第二步和第三步可以合并为一步，因为我们可以直接使用两次AT TIME ZONE。 另一种写法（更简洁）： 我们可以将date字段直接转换为timestamp without time zone，然后将其视为原始时区（UTC）的时间，然后转换为目标时区的时间戳（不带时区），最后取日期部分。 因此，完整的查询语句可能是这样的： ```sql SELECT d AS original_date, ((d::timestamp AT TIME ZONE 'UTC') AT TIME ZONE 'America/New_York')::date AS new_york_date FROM t; ``` 注意：这里假设原始数据是按UTC存储的。如果原始数据存储的时区不是UTC，而是其他时区（比如数据库的时区设置），那么应该将'UTC'替换为原始时区。 另外，如果数据库的时区设置就是原始时区，我们可以使用当前会话的时区（即不指定原始时区，而是使用会话的时区设置）。但是，为了明确，建议指定原始时区。 引用[1]中提到，我们应该避免在查询中频繁进行时区转换，因为这样可能影响性能。因此，如果可能，在应用层处理时区转换或者确保数据库的时区设置正确。 此外，如果我们需要在查询条件中考虑时区转换，同样的转换逻辑也可以用在WHERE子句中，但要注意性能，因为函数使用可能会导致索引失效（如果我们在原始字段上建立了索引，那么对字段进行转换操作后索引可能不会被使用）。因此，引用[1]建议合理设置数据库时区，并尽量在存储时使用一致的时区（如UTC）。 最后，关于时区名称，PostgreSQL支持IANA时区数据库，如'Asia/Shanghai'，'America/New_York'等。完整的时区列表可以通过以下查询获取： ```sql SELECT * FROM pg_timezone_names; ``` 总结：对于date类型的时区转换，我们需要先将date转换为时间戳，然后通过两次AT TIME ZONE转换（第一次指定原始时区，第二次指定目标时区），最后再转换为date类型。 注意：如果转换后的时间戳跨越了日期（比如从UTC转到美国东部时间，会减去4或5个小时），那么日期可能会变化。 示例：将UTC日期'2023-01-01'转换为美国东部时间（EST）： 原始日期：2023-01-01（UTC）对应的时间戳是2023-01-01 00:00:00 UTC 转换为美国东部时间（冬季是EST，UTC-5）：2022-12-31 19:00:00（所以日期变成了2022-12-31） 因此，转换后的日期是2022-12-31。 所以，在转换时要注意日期可能变化。 如果我们的需求是保持日期不变，那么可能需要重新考虑设计（比如存储为timestamp with time zone，并记录完整的时间），因为时区转换必然会影响日期。 如果应用场景要求日期在目标时区中显示为同一天，那么我们可以考虑在应用层处理，或者使用不同的转换逻辑（例如，只转换时间部分，但保持日期不变？这通常不符合时区转换规则）。 因此，请根据实际需求谨慎使用。 下面是一个完整的示例： 假设表t： ```sql CREATE TABLE t ( id serial PRIMARY KEY, event_date date ); ``` 插入数据： ```sql INSERT INTO t (event_date) VALUES ('2023-01-01'); ``` 查询转换为美国东部时间后的日期： ```sql SELECT event_date AS original_date, ((event_date::timestamp AT TIME ZONE 'UTC') AT TIME ZONE 'America/New_York')::date AS new_york_date FROM t; ``` 结果： original_date | new_york_date ---------------+--------------- 2023-01-01 | 2022-12-31 如果这不是你想要的结果，你可能需要重新考虑。也许你只是想在目标时区中显示同一天，那么你可以考虑在转换后的时间戳上加上12小时（这样日期就不会因为时差而提前一天），但这并不通用。另一种做法是，不要转换为日期，而是保留为时间戳，并只显示日期部分，但这样转换后的日期就是根据时间戳的日期部分。 所以，请根据实际业务需求来决定。 另外，如果原始数据存储的时区不是UTC，而是其他时区，比如'Asia/Shanghai'（UTC+8），那么转换到美国东部时间： ```sql SELECT event_date AS original_date, ((event_date::timestamp AT TIME ZONE 'Asia/Shanghai') AT TIME ZONE 'America/New_York')::date AS new_york_date FROM t; ``` 例如，对于'2023-01-01'（在亚洲上海时区是2023-01-01 00:00:00+08），转换为UTC时间是2022-12-31 16:00:00 UTC，再转换为美国东部时间是2022-12-31 11:00:00（所以日期是2022-12-31）。 因此，请确保你清楚数据存储的原始时区。 最后，引用[1]中建议，合理设置数据库时区，使用AT TIME ZONE进行时区转换，并避免在查询中频繁进行时区转换[^1]。 如果性能很重要，可以考虑在数据库设计时就直接存储为timestamp with time zone类型，并存储UTC时间，这样在转换时可以利用索引（通过表达式索引）来提高性能。 相关问题： 1. 在PostgreSQL中，如何设置数据库的默认时区？ 2. 如何创建在时区转换后字段上的索引？ 3. 在PostgreSQL中，date类型和timestamp类型有什么区别？ 4. 如何避免时区转换导致的性能问题？</think>### PostgreSQL 查询 Date 类型字段并进行时区转换的完整指南 在 PostgreSQL 中处理日期类型字段的时区转换需要特别注意数据类型特性。以下是详细解决方案： #### 📅 核心问题分析 1. **date 类型特性**： - `date` 类型仅存储日历日期（年-月-日），**不包含时间或时区信息**[^1][^2] - 无法直接使用时区函数转换（如 `AT TIME ZONE`） 2. **转换逻辑**： ```mermaid graph LR A[date字段] --> B[转换为timestamp] B --> C[添加时区信息] C --> D[AT TIME ZONE转换] D --> E[提取日期部分] ``` #### 🔧 完整解决方案 ##### 方法 1：单步转换（推荐） ```sql SELECT your_date_field, -- 原始日期字段 ( (your_date_field::timestamp AT TIME ZONE 'UTC') -- 假设原始数据为UTC AT TIME ZONE 'Asia/Shanghai' -- 转换到目标时区 )::date AS converted_date -- 提取日期部分 FROM your_table; ``` ##### 方法 2：分步转换（更清晰） ```sql WITH conversion_step AS ( SELECT your_date_field, -- 添加时间部分并指定原始时区 (your_date_field || ' 00:00:00')::timestamp AT TIME ZONE 'UTC' AS utc_timestamptz FROM your_table ) SELECT your_date_field AS original_date, (utc_timestamptz AT TIME ZONE 'America/New_York')::date AS new_york_date, -- 纽约时区 (utc_timestamptz AT TIME ZONE 'Europe/Paris')::date AS paris_date -- 巴黎时区 FROM conversion_step; ``` #### ⚠️ 关键注意事项 1. **时区格式**： - 使用 IANA 时区标识（如 `Asia/Shanghai`） - 支持列表查询：`SELECT * FROM pg_timezone_names;` 2. **原始时区假设**： - 示例中假设原始数据存储在 UTC 时区 - 根据实际情况调整 `AT TIME ZONE 'UTC'` 中的时区标识 3. **夏令时处理**： ```sql -- 验证特定日期的时区偏移 SELECT '2023-07-01'::timestamp AT TIME ZONE 'America/New_York' AS summer_time, '2023-01-01'::timestamp AT TIME ZONE 'America/New_York' AS winter_time; ``` 4. **索引优化**： ```sql -- 创建函数索引提高查询性能 CREATE INDEX idx_converted_date ON your_table USING btree ((((your_date_field::timestamp AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Shanghai')::date)); ``` #### 🌐 时区转换原理 ```sql /* 转换过程分解 */ SELECT '2023-12-25'::date AS original_date, -- 原始日期 '2023-12-25'::timestamp AS timestamp_no_tz, -- 添加时间部分 ('2023-12-25'::timestamp AT TIME ZONE 'UTC') AS utc_timestamptz, -- 添加时区 (('2023-12-25'::timestamp AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Tokyo') AS tokyo_time, -- 东京时间 (('2023-12-25'::timestamp AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Tokyo')::date AS tokyo_date; -- 最终日期 ``` #### 💡 最佳实践建议 1. **存储策略**： - 优先使用 `timestamptz` 类型存储带时区的时间戳[^1] - 仅当确定不需要时区信息时使用 `date` 类型 2. **性能优化**： - 避免在 WHERE 子句中直接转换字段（影响索引使用） - 对转换结果创建物化视图处理高频查询 3. **应用层处理**： ```java // Java 示例：结果集处理 LocalDate originalDate = resultSet.getObject("original_date", LocalDate.class); ZonedDateTime tokyoTime = originalDate.atStartOfDay(ZoneId.of("UTC")) .withZoneSameInstant(ZoneId.of("Asia/Tokyo")); LocalDate convertedDate = tokyoTime.toLocalDate(); ``` > **重要提示**：时区转换可能导致日期变化（跨时区日期变更线），业务逻辑需考虑此场景[^1][^2]。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值