postgreSQL使用

字符串

select md5('2345234');

时间

查询当前日期

select current_date;

查询当前时间

select current_time;

当前的年月日时分秒

select current_timestamp;
select now();
select localtimestamp;

获取一个日期字符串的day

select extract(day from now();

获取时间类型的day

select extract(day from now());

月和年依次类推 month 、year

查询复杂时间

//查询某个时间是一年的第几天
select extract(doy from timestamp '2021-09-10 10:18:50');
// 查询某个时间的一周的星期几
select extract(dow from timestamp '2019-08-10');
//查询是第几季度
select extract(quarter from now());
// 查询是第几周
select extract(WEEK from now());

日期运算

select date '2012-09-28' + integer '10'

select date '2012-09-28' - integer '10'

select date '2012-09-28' - date '2012-08-01' as "newDate";

select  timestamp '2019-08-01 12:12:12' +  '2 day';

数字

//求绝对值
select abs(-1);

//求圆周率
select pi();

//求平方根
select sqrt(9);

//求余
select mod(12, 5);

//求最接近的最大整数
select  ceil(-zhgen3.35)
//求四舍五入的数字
select round(-12.3)
//求2的平方根
select pow(2,2);
//求2的负平方根
select power(2,-2);
//求随机数
select  random();

with和case综合用法

查询某人某天的出勤情况

with leave_chiefs as (
    select count(1), grid_chief_id
    from patrol_grid_chief_attendance where state = 'LEAVE' and date = DATE '2019-08-15'
    group by grid_chief_id
)
select pgc.id,
    case (select 100 from leave_chiefs lc where lc.grid_chief_id = pgc.id) when 100 then 'LEAVE' else 'ATTEND' end as state
from patrol_grid_chief pgc;

这样可以查出出勤表

从json里面查某个字段

'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb  

在mapper里面这样查询

按月统计

  select
         to_char(create_time, 'YYYY-MM') as d ,
         count(id)  as  total_call
  from  patrol_event
  group by d;

按小时统计

  select
         to_char(create_time, 'YYYY-MM-DD  HH24') as d ,
         count(id)  as  total_call
  from  patrol_event
  group by d;

按秒分组

  select
         to_char(create_time, 'YYYY-MM-DD  HH24:MI:SS') as d ,
         count(id)  as  total_call
  from  patrol_event
  group by d;

mybatis转换

在mybatis里面，日期要转一下

    <select id="getMonthSummaryForTodayLeaf" resultType="com.swcote.patrolStaffStatistics.dto.GridStaffPatrolMonthlySummary">
        select
        to_char(start_time, 'YYYY-MM-01')::date as "month",
        sum(extract(epoch from (end_time - start_time)) / 3600) as sumHours,
        sum(path_length) as sumLength
        from patrol_grid_trace
        group by "month"
    </select>

日期运算

某个时间加3年

select date '2015-04-10' + interval '3 years';

当前时间前一年

select now() - interval '1 years';

查询两个时间的差值

可以看到差了多少个月多少天

select age('2001-04-10', timestamp '1957-06-13')

sql循环

计算1到100之间的总和

WITH RECURSIVE t(n) as (
    values (1)
  union all
    select n+1 from t where n < 100
)
select sum(n) from t

sql递归查询 （秒）

 WITH RECURSIVE view_pg (id, name, grade, parent) as (
    SELECT id, name, grade, parent
    from patrol_grid g
    where g.id = 276
    and g.tenant = 'MWZHZLZX'
    UNION ALL
    SELECT c.id, c.name, c.grade, c.parent
    from patrol_grid as c
    join view_pg as p on p.id = c.parent
  )
 select * from (
    select * from view_pg as pg
    join patrol_grid_trace as pgt on pgt.grid_id = pg.id
    and pgt.tenant = 'MWZHZLZX'
 ) as tol;

查询两个时间点之间的小时数

select extract(epoch from (timestamp '2013-12-14 12:00:00' - timestamp '2013-12-11 4:00:00')) / 3600;

查询两个时间点之间的天数和小时数

select extract(day from t) * 24 as days , extract(hour from t) as hours
from (select (timestamp '2013-12-14 12:00:00' - timestamp '2013-12-11 4:00:00') as t ) as a;