時間運算
時間運算
Categories:
擷取出指定時間格式的時間資料
date_trunc 時間截斷
| 參數 | 說明 | SQL | 結果 |
|---|---|---|---|
| microseconds | 微秒 | SELECT date_trunc(‘microseconds’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 2021-05-17 19:37:41 |
| milliseconds | 毫秒 | SELECT date_trunc(‘milliseconds’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 2021-05-17 19:37:41 |
| second | 秒 | SELECT date_trunc(‘second’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 2021-05-17 19:37:41 |
| minute | 分鐘 | SELECT date_trunc(‘minute’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 2021-05-17 19:37:00 |
| hour | 小時 | SELECT date_trunc(‘hour’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 2021-05-17 19:00:00 |
| day | 日 | SELECT date_trunc(‘day’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 2021-05-17 00:00:00 |
| month | 月 | SELECT date_trunc(‘month’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 2021-05-01 00:00:00 |
| year | 年 | SELECT date_trunc(‘year’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 2021-01-01 00:00:00 |
| week | 週 | SELECT date_trunc(‘week’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 2021-05-17 00:00:00 |
| week | 週 | SELECT date_trunc(‘week’, TIMESTAMP ‘2021-05-16 19:37:41.571’); | 2021-05-10 00:00:00 |
| quarter | 季 | SELECT date_trunc(‘quarter’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 2021-04-01 00:00:00 |
| decade | 十年 | SELECT date_trunc(‘decade’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 2020-01-01 00:00:00 |
| decade | 十年 | SELECT date_trunc(‘decade’, TIMESTAMP ‘1921-05-17 19:37:41.571’); | 1920-01-01 00:00:00 |
| century | 世紀 | SELECT date_trunc(‘century’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 2001-01-01 00:00:00 |
| century | 世紀 | SELECT date_trunc(‘century’, TIMESTAMP ‘1921-05-17 19:37:41.571’); | 1901-01-01 00:00:00 |
SQL 範例
select date_trunc('year', u."created_at"), u."created_at"
from user u
date_part 擷取時間部分資料
| 參數 | 說明 | SQL | 結果 |
|---|---|---|---|
| microseconds | 微秒 | SELECT date_part(‘microseconds’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 41571000 |
| milliseconds | 毫秒 | SELECT date_part(‘milliseconds’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 41571.0 |
| second | 秒 | SELECT date_part(‘second’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 41.571 |
| minute | 分鐘 | SELECT date_part(‘minute’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 37 |
| hour | 小時 | SELECT date_part(‘hour’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 19 |
| day | 日 | SELECT date_part(‘day’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 17 |
| month | 月 | SELECT date_part(‘month’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 5 |
| year | 年 | SELECT date_part(‘year’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 2021 |
| week | 週 | SELECT date_part(‘week’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 20 |
| quarter | 季 | SELECT date_part(‘quarter’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 2 |
| decade | 十年 | SELECT date_part(‘decade’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 202 |
| decade | 十年 | SELECT date_part(‘decade’, TIMESTAMP ‘2011-05-17 19:37:41.571’); | 201 |
| century | 世紀 | SELECT date_part(‘century’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 21 |
| century | 世紀 | SELECT date_part(‘century’, TIMESTAMP ‘1921-05-17 19:37:41.571’); | 20 |
| millennium | 千年 | SELECT date_part(‘millennium’, TIMESTAMP ‘2021-05-17 19:37:41.571’); | 3 |
| millennium | 千年 | SELECT date_part(‘millennium’, TIMESTAMP ‘1921-05-17 19:37:41.571’); | 2 |
| millennium | 千年 | SELECT date_part(‘millennium’, TIMESTAMP ‘921-05-17 19:37:41.571’); | 1 |
SQL 範例
select date_part('year', u."created_at"), u."created_at"
from user u
EXTRACT 擷取時間部分資料
| 參數 | 說明 | SQL | 結果 |
|---|---|---|---|
| microseconds | 微秒 | SELECT EXTRACT(MICROSECONDS FROM TIMESTAMP ‘2021-05-17 19:37:41.571’); | 41571000 |
| milliseconds | 毫秒 | SELECT EXTRACT(MILLISECONDS FROM TIMESTAMP ‘2021-05-17 19:37:41.571’); | 41571.0 |
| second | 秒 | SELECT EXTRACT(SECOND FROM TIMESTAMP ‘2021-05-17 19:37:41.571’); | 41.571 |
| minute | 分鐘 | SELECT EXTRACT(MINUTE FROM TIMESTAMP ‘2021-05-17 19:37:41.571’); | 37 |
| hour | 小時 | SELECT EXTRACT(HOUR FROM TIMESTAMP ‘2021-05-17 19:37:41.571’); | 19 |
| day | 日 | SELECT EXTRACT(day FROM TIMESTAMP ‘2021-05-17 19:37:41.571’); | 17 |
| month | 月 | SELECT EXTRACT(MONTH FROM TIMESTAMP ‘2021-05-17 19:37:41.571’); | 5 |
| year | 年 | SELECT EXTRACT(year FROM TIMESTAMP ‘2021-05-17 19:37:41.571’); | 2021 |
| week | 週 | SELECT EXTRACT(WEEK FROM TIMESTAMP ‘2021-05-17 19:37:41.571’); | 20 |
| quarter | 季 | SELECT EXTRACT(quarter FROM TIMESTAMP ‘2021-05-17 19:37:41.571’); | 2 |
| decade | 十年 | SELECT EXTRACT(decade FROM TIMESTAMP ‘2021-05-17 19:37:41.571’); | 202 |
| decade | 十年 | SELECT EXTRACT(decade FROM TIMESTAMP ‘2011-05-17 19:37:41.571’); | 201 |
| century | 世紀 | SELECT EXTRACT(century FROM TIMESTAMP ‘2021-05-17 19:37:41.571’); | 21 |
| century | 世紀 | SELECT EXTRACT(century FROM TIMESTAMP ‘1921-05-17 19:37:41.571’); | 20 |
| millennium | 千年 | SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP ‘2021-05-17 19:37:41.571’); | 3 |
| millennium | 千年 | SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP ‘1921-05-17 19:37:41.571’); | 2 |
| millennium | 千年 | SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP ‘921-05-17 19:37:41.571’); | 1 |
SQL 範例
SELECT EXTRACT(year FROM u."created_at"), u."created_at"
from user u