postgresql 實現得到時間對應周的周一案例
兩種方法:
第一種:
DO $$ declare d int; declare d1 varchar(100); declare d2 varchar(100); declare d3 date; declare d4 date; begin d3:= CURRENT_DATE; d1:='select date'''|| d3 ||''''; d:=(SELECT EXTRACT(DOW FROM d3))-1; d2:=d1 || '-INTERVAL ''' || d || ' day '' '; EXECUTE d2 into d4; RAISE NOTICE 'ok %',d4; end$$
結果:
[SQL]DO $$ declare d int; declare d1 varchar(100); declare d2 varchar(100); declare d3 date; declare d4 date; begin d3:= CURRENT_DATE; d1:='select date'''|| d3 ||''''; d:=(SELECT EXTRACT(DOW FROM d3))-1; d2:=d1 || '-INTERVAL ''' || d || ' day '' '; EXECUTE d2 into d4; RAISE NOTICE 'ok %',d4; end$$ NOTICE: ok 2016-06-13 時間: 0.004s 受影響的行: 0
解析:
declare :聲明變量
CURRENT_DATE : 得到當前日期
SELECT CURRENT_DATE;
結果:
date 2016-06-12
extract :從時間中抽出相應的字段
DOW 一周裡的第幾天 (sunday =0 saturday=6)
格式:
EXTRACT(field FROM source)
當前日期是一周裡面的第幾天
SELECT EXTRACT(DOW FROM CURRENT_DATE);
結果:
date_part 0
INTERVAL :時間間隔類型
EXECUTE :執行一個準備好的查詢
RAISE NOTICE :把結果顯示出來
第二種:
SELECT CURRENT_DATE +cast(-1*(TO_NUMBER(to_char(CURRENT_DATE,'D'),'99')-2) ||' days' as interval);
結果:
?column? 2016-06-13 00:00:00
解析:
TO_NUMBER 將一個字符串轉換成數字
格式:
TO_NUMBER(string,format)
-- 一周裡的日子(1-7;周日是1) select to_char(CURRENT_DATE ,'D') DDD 一年裡的日子(001-366) DD 一個月裡的日子(01-31) D 一周裡的日子(1-7;周日是1) select to_char (to_date('2016-06-12','yyyy-mm-dd'),'D') select to_number(‘1.1','9.99') from dual; 1.1 select to_number(‘1.121','9.99') from dual; 1.12 -- 將得到的字符串轉換成數字 select TO_NUMBER(to_char(CURRENT_DATE,'D'),'99') -- 因為得到的星期一為2,所以要減去2 select TO_NUMBER(to_char(CURRENT_DATE,'D'),'99')-2 -- 將得到的數字乘以 -1 比如例子中:-1*3 就是 -3 ,也就是減去 3天 select cast(-1*3 || 'days' as interval) -- 就是將當天減去0天 得到瞭星期一的日期 select cast(-1*0 || 'days' as interval) + CURRENT_DATE SELECT to_char(CURRENT_DATE +cast(-1*(TO_NUMBER(to_char(CURRENT_DATE,'D'),'99')-2) ||' days' as interval),'yyyy-mm-dd');
補充:Postgresql數據數據庫中按日、月、周、年、時、分,30分鐘的統計解決方案
對要統計的時間字段進行字符轉換處理,再按照其分組即可實現對數據進行日,周,月,年,時,分,秒的統計
1、按日統計
to_char( h.row_date, 'yyyy-MM-dd' ) AS row_date2 GROUP BY to_char( h.row_date, 'yyyy-MM-dd' )
2、按月統計
to_char(h.row_date, 'yyyy-MM' ) AS row_date2 GROUP BY to_char(h.row_date, 'yyyy-MM' )
3、按年統計
to_char( h.row_date,'yyyy' ) AS row_date2 GROUP BY to_char( h.row_date,'yyyy' )
4、按小時統計
to_char( h.row_date, 'yyyy-MM-dd HH' ) AS row_date2 GROUP BY to_char( h.row_date, 'yyyy-MM-dd HH' )
5、按分鐘統計
to_char( h.row_date, 'yyyy-MM-dd HH:mm' ) AS row_date2 GROUP BY to_char( h.row_date, 'yyyy-MM-dd HH:mm' )
6、按周統計
按周統計最簡單法
對時間row_date字段做處理,變成對應日期周一時間,然後按這個周一的時間去統計。減1的操作表示為對應日期的星期一,減1,2,3,4,5,6,7分別是對應日期的周一,周二,周三,周四,周五、周六、周日。
to_char( h.row_date-(extract (dow from h.row_date) - 1 ||'day')::interval,'yyyy-MM-dd') row_date
然後按上面的語句分組統計即可實現按周統計,下面對應分組函數
GROUP BY to_char(h.row_date-(extract (dow from h.row_date) - 1 ||'day')::interval,'yyyy-MM-dd')
按周統計之方法二(較復雜,不建議使用)
to_char(h.row_date, 'yyyy' ) || EXTRACT ( week FROM h.row_date ) :: INTEGER ASrow_date2
獲取到數據庫輸出的字段中的年份和周數。
String row_date=rs.getString("row_date2"); //獲取數據庫輸出日期的年份 int year=Integer.parseInt(row_date.substring(0, 4)); //獲取數據庫輸出日期的周數 if(row_date.length()>=6){ week=Integer.parseInt(row_date.substring(4,6));} else{ week=Integer.parseInt(row_date.substring(4,5)); } String row_date2=getFirstDayOfWeek(year, week); trafficMap.put("row_date", row_date2);
將查詢出的內容日期轉換成當周周一的時間
//將周統計中獲取的如201636,表示2016年36周,獲取其周一的時間 public String getFirstDayOfWeek(int year, int week) { // 先滾動到該年 nows.set(Calendar.YEAR, year); // 滾動到周 nows.set(Calendar.WEEK_OF_YEAR, week); // 得到該周第一天 nows.set(Calendar.DAY_OF_WEEK, 2); String firstDay = df.format(nows.getTime()); return firstDay; }
7、按30分鐘進行統計
case when substr( to_char(h.row_date, 'yyyy-mm-dd hh24:mi'),15, 16) :: integer <=30 then to_char(h.row_date, 'yyyy-mm-dd hh24')||':30' else to_char( h.row_date, 'yyyy-mm-ddhh24' )||':60' end as row_date2 GROUP BY case when substr( to_char(h.row_date, 'yyyy-mm-dd hh24:mi'),15, 16) :: integer <=30 then to_char(h.row_date, 'yyyy-mm-dd hh24')||':30' else to_char( h.row_date, 'yyyy-mm-ddhh24' )||':60' end
以上為個人經驗,希望能給大傢一個參考,也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方,望不吝賜教。
推薦閱讀:
- postgresql數據庫根據年月查詢出本月的所有數據操作
- PostgreSQL數據類型格式化函數操作
- Oracle根據時間查詢的一些常見情況匯總
- PostgreSQL中的日期/時間函數詳解
- Postgresql的pl/pgql使用操作–將多條執行語句作為一個事務