4.5 SQL的数据类型与模式

发表于 更新于 分类于 ， ， ， Waline： 本文字数： 2.7k 阅读时长 ≈ 2 分钟

4.5 SQL的数据类型与模式

在第3章中,我们介绍了一些SQL支持的固有数据类型,如整数类型、实数类型和字符类型。SQL还支持一些其他的固有数据类型,我们将在下面描述。我们还将描述如何**在SQL中创建基本的用户定义类型**。

4.5.1 SQL中的日期和时间类型

除了我们在3.2节介绍过的基本数据类型以外,SQL标准还支持与日期和时间相关的几种数据类型:
date:日历日期,包括年(四位)、月和日。
time:一天中的时间,包括小时、分和秒。可以用变量time(p)来表示秒的小数点后的数字位数(这里默认值为0)。通过指定time with timezone,还可以把时区信息连同时间一起存储。
timestamp:date和time的组合。可以用变量timestamp(p)来表示秒的小数点后的数字位数(这里默认值为6)。如果指定with timezone,则时区信息也会被存储。

日期和时间类型的值可按如下方式说明:

1
2
3
date '2001-04-25'
time '09:30:00'
timestamp '2001-04-25 10:29:01.45'

日期类型必须按照如上年月日的格式顺序指定。time和timestamp的秒部分可能会有小数部分,像上述时间戳中的情况一样。

字符串转成日期和时间类型

我们可以利用cast e as t形式的表达式来将一个字符串(或字符串表达式)e转换成类型t,其中t是date、time、 timestamp中的一种。字符串必须符合正确的格式,像本段开头说的那样。当需要时,时区信息可以从系统设置中得到。

从日期和时间中提取年月日时分秒等信息

我们可以利用extract(field from d),从date或time值d中提取出单独的域,这里的域可以是year、 month、day、hour、 minute或者second中的任意一种。时区信息可以用timezone_hour和timezone_minute来提取。

1
2
3
4
5
6
7
mysql> select extract(year from current_date());
+-----------------------------------+
| extract(year from current_date()) |
+-----------------------------------+
|                              2019 |
+-----------------------------------+
1 row in set (0.02 sec)

SQL中获取当然日期和事件的函数

SQL定义了一些函数以获取当前日期和时间。例如,

  • current_date返回当前日期,
  • current_time返回当前时间(带有时区),
  • 还有localtime返回当前的本地时间(不带时区)。
  • 时间戳(日期加上时间)由current_timestamp(带有时区)
  • 以及localtimestamp(本地日期和时间,不带时区)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2019-10-21     |
+----------------+
1 row in set (0.02 sec)

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 18:44:48       |
+----------------+
1 row in set (0.01 sec)

mysql> select localtime();
+---------------------+
| localtime()         |
+---------------------+
| 2019-10-21 18:45:00 |
+---------------------+
1 row in set (0.02 sec)

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2019-10-21 18:45:11 |
+---------------------+
1 row in set (0.02 sec)

mysql> select localtimestamp();
+---------------------+
| localtimestamp()    |
+---------------------+
| 2019-10-21 18:45:29 |
+---------------------+
1 row in set (0.03 sec)

SQL支持的数据运算

SQL允许在上面列出的所有类型上进行比较运算,也允许在各种数字类型上进行算术运算和比较运算。SQL还支持interval数据类型,它允许在日期、时间和时间间隔上进行计算。

什么是时间间隔类型

例如,假设x和y都是date类型,那么x-y就是时间间隔类型,其值为从日期x到日期y间隔的天数。类似地,在日期或时间上加减一个时间间隔将分别得到新的日期或时间