sql時間用法

PostgreSQL 提供了大量用于獲取系統當前日期和時間的函數，例如 current_date、current_time、current_timestamp、clock_timestamp()、localtimestamp、now()、statement_timestamp()等；同時還支持延遲語句執行的 pg_sleep()等函數

數據庫有很多，mysql、sqlserver等存在存儲過程，所需要用到的函數各不相同，本文列舉一下PostgreSQL的語法。
日期和時間函數主要用來處理日期和時間值，一般的日期函數除了使用date類型的參數外，也可以使用datetime或者timestamp類型，但會忽略這些值的時間部分。相同的以time類型值為參數的函數，可以接受timestamp類型的參數，但會忽略日期部分。

獲取當前日期的函數&&獲取當前時間的函數

1、獲取當前日期的函數和獲取當前時間的函數：current_date和current_time

2、調用該函數時不需要在函數名后加括號。該日期是服務器的日期，不是客戶端的日期。

注意：

上面所有的函數，包括 CURRENT_DATE，返回的都是當前事務開始的時間。在同一個事務期間，多次調用相同的函數將會返回相同的值，結果不會隨著時間增加。這一點與其他數據庫的實現可能不同。

獲取當前日期的函數

current_date函數：的作用是將當前日期按照“YYYY-MM-DD”格式的值返回，具體格式根據函數用在字符串或是數字語境中而定的。

select current_date;

獲取當前時間的函數

current_time函數：的作用是將當前時間以“HH:MM:SS”的格式返回，具體格式根據函數用在字符串或是數字語境中而定的。

select current_time;;

localtime函數：的作用是將當前時間以“HH:MM:SS”的格式返回并且localtime沒有時區。

select localtime;

獲取當前日期加時間的函數

基本上current_timestamp,transaction_timestamp()和now()完全相同。current_timestamp是一個函數的語法奇怪，沒有尾對括號，這是根據sql標準。

如果未在sql語句中聲明函數調用的列別名(需要一個)，則別名默認為函數的名稱。在內部，標準sql current_timestamp使用now()實現，并在您查看默認列別名時顯示。

函數： current_timestamp

select current_timestamp;

函數： transaction_timestamp()

transaction_timestamp() 等價于 current_timestamp，但是作用更加明確。

select transaction_timestamp();

函數： statement_timestamp()

statement_timestamp()返回當前語句的開始時間，更準確地說，應該是接收到客戶端最新命令的時間。與transaction_timestamp相比，對于事務中的第一個命令返回的結果相同，但隨后再執行 statement_timestamp() 將會返回不同的值。

select transaction_timestamp();
select statement_timestamp();

BEGIN;
	BEGIN
	SELECT statement_timestamp();   
	     
	SELECT pg_sleep(3);
	
	SELECT statement_timestamp();
	
	COMMIT;
COMMIT

兩次執行結果之間相差了 3 秒左右。

當我們在存儲過程（Stored Procedure）中進行調試時，通常需要打印不同語句消耗的時間；此時就需要使用 statement_timestamp()，而不能使用 CURRENT_TIMESTAMP 或者 transaction_timestamp()：

CREATE OR REPLACE sp_test
...
DECLARE
  lts_systimestamp timestamp;
BEGIN;
  lts_systimestamp := statement_timestamp();
  ...
  RAISE NOTICE 'Step 1 take time: %', statement_timestamp() - lts_systimestamp;
  ...
END;

函數： clock_timestamp()

clock_timestamp()與statement_timestamp()相比，后者是接收到客戶端信息的時候更新時間，而clock_timestamp()接受到信息以后每次調用這個函數打印的時間戳都不一樣。返回的是實際時間。

SELECT clock_timestamp();
SELECT clock_timestamp() FROM generate_series(1,10);

clock_timestamp() 返回當前實際的時間，即使在同一個 SQL 語句中也可能返回不同的值：

因為我使用的是Dbeaver客戶端，所以返回的精度不高。正常來說，查詢語句在 1 秒鐘內返回了 10 條記錄，但是每條記錄產生的時間都不相同。

函數： timeofday()

timeofday()與clock_timestamp()一樣返回的是實際的時間，不過timeofday()函數返回類型是一個格式化的字符串，而不是 timestamp with time zone：

SELECT timeofday();
SELECT timeofday() FROM generate_series(1,10);

函數： now()

now()與transaction_timestamp() 一樣

select now() ;

注意： now() 是 PostgreSQL 中與 transaction_timestamp() 等價的一個傳統函數，同一個事務中的結果不會改變。

另外，所有的日期/時間數據類型都支持使用字面值’now’指定當前日期和時間（當前事務開始時間）。因此，以下語句效果相同：

SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now';

另外，PostgreSQL 還提供了其他幾個特殊的日期和時間字面值：

以上函數分別返回 UTC 1970 年 1 月 1 日零點、今天午夜、明天午夜、昨天午夜以及 UTC 零點。

延遲執行

以下函數可以用于延遲服務器進行的操作：

pg_sleep(seconds)
pg_sleep_for(interval)
pg_sleep_until(timestamp with time zone)

函數： pg_sleep()

pg_sleep 將當前會話的進行暫停指定的秒數。seconds 的類型為 double precision，所以支持小數秒。我們在面前使用了該函數。

select pg_sleep(10);

函數： pg_sleep_for ()

pg_sleep_for 執行一個延遲的時間間隔，通常用于指定一個較大的延遲。

select pg_sleep_for('2 minutes');

函數： pg_sleep_until()

pg_sleep_until 可以用于指定一個進程的喚醒時間。

select pg_sleep_until('tomorrow 05:00');

以上示例分別暫停 1.5 秒、5 分鐘以及直到明天 5 點，在特定的存儲過程里面，可以滿足一些需求。
==注意：==使用這些延遲函數時，確保當前會話沒有鎖定過多的資源；否則，其他會話將會一直等待，導致系統性能的下降。

時間和日期函數

to_char(timestamp, text)

to_char(timestamp, text)

將時間戳轉換為字符串。

SELECT to_char(current_timestamp, 'HH:MI:SS')

to_char(int, text)

to_char(int, text)

將整數轉換為字符串。

select to_char(125, '999');

to_char(double precision, text)

to_char(double precision, text)

將實數或雙精度數轉換為字符串。

select to_char(125.8::real, '999D9');

to_date(text, text)

to_date(text, text)

將字符串轉換為日期。

select to_date('22 Oct 2022', 'DD Mon YYYY');

select to_date('2022-10-20', 'yyyy-mm-dd');

to_number(text, text)

to_number(text, text)

將字符串轉換為數字。

select to_number('12,454.8-', '99G999D9S');

to_timestamp(text, text)

to_timestamp(text, text)

將字符串轉換為時間戳。

select to_timestamp('05 Dec 2000', 'DD Mon YYYY')

date - date

date - date

日期的減法運算。

select date '2020-01-01' - date '2020-01-05'

date_part(text, timestamp)

date_part(text, timestamp)

從時間戳中獲取子字段。

select date_part('hour', timestamp '2001-02-16 20:38:40')

date_trunc(text, timestamp)

date_trunc(text, timestamp)

截斷時間戳到指定精度。

select date_trunc('hour', timestamp '2001-02-16 20:38:40')

extract(field from timestamp)

extract(field from timestamp)

從時間戳中獲取子字段。

select extract(hour from timestamp '2001-02-16 20:38:40')

make_date(year int, month int, day int)

make_date(year int, month int, day int)

使用年月日創建日期。

select make_date(2013, 7, 15);

使用timestamp來比較時間

使用timestamp來比較時間

select '2020-01-01' < '2019-01-01'::timestamp+ '360 day'

使用interval添加時間

INTERVAL ‘3’ DAY 時間間隔為3天

INTERVAL ‘2’ HOUR 時間間隔為2小時

總結