pgsql对时间做格式化统计 将时间戳转换成可视化时间格SELECT *, mc_db_time, fetch_time, ( mc_db_time :: BIGINT - fetch_time :: BIGINT ) AS sum_time, to_char( to_timestamp( fetch_time :: BIGINT ), 'yyyy-MM-dd hh24:MI:ss' ) AS format_fetch_time FROM "public"."log_history" WHERE "table_name" = 'user_master' AND "primary_id" LIKE'%data-sync-%' ORDER BY fetch_time DESC LIMIT 100;针对时间做格式化筛选SELECT COUNT ( user_vuclip_accounts.* ), EXTRACT ( DAY FROM user_vuclip_accounts.created_at ) log_day FROM user_vuclip_accounts WHERE EXTRACT ( YEAR FROM user_vuclip_accounts.created_at ) = EXTRACT ( YEAR FROM NOW( ) ) AND EXTRACT ( month FROM user_vuclip_accounts.created_at ) = EXTRACT ( month FROM NOW( ) - interval '1 month' ) GROUP BY EXTRACT ( DAY FROM user_vuclip_accounts.created_at );

mysql查询重复信息并删除id较大的记录 查询是否有重复的记录SELECT * FROM 表名 GROUP BY 字段名 HAVING COUNT(1) > 1;查看是否是需要删除的记录SELECT * FROM 表名 where id not in ( SELECT id from (SELECT MIN(id) id FROM 表名 GROUP BY 重复字段名) a ); 删除对应的记录DELETE FROM 表名 where id not in ( SELECT id from (SELECT MIN(id) id FROM 表名 GROUP BY 重复字段名) a );

mysql 为指定账号添加指定db的权限 1. 创建新用户CREATE USER 'user1'@'%' IDENTIFIED BY '123456';'%' - 所有情况都能访问‘localhost’ - 本机才能访问’111.222.33.44‘ - 指定 ip 才能访问2. 修改密码update mysql.user set password=password('新密码') where user='user1';3. 给该用户添加权限grant all privileges on 想授权的数据库.* to 'user1'@'%';all 可以替换为 select,delete,update,create,drop4. 删除用户Delete FROM mysql.user Where User='user1';5. 刷新全新flush privilege;

redis 批量模糊删除key 集群模式命令：redis-cli -c -h xxx.com KEYS "test:*" | xargs redis-cli -c -h xxx.com DEL普通模式命令：redis-cli -h xxx.com KEYS "test:*" | xargs redis-cli -h xxx.com DEL{message type="error" content="以上的方式如果是大key， 就不推荐使用， 可以使用scan命令去匹配扫描"/}redis-cli -h xxx.com -p 6379 --scan --pattern test:* | xargs redis-cli -h xxx.com -p 6379 unlink

postgrsql update， 给指定字段批量加前缀 使用||连接update user_accounts set principal = ('2022-09-06-' || principal) where user_id NOT IN (select id from users) ;