sql语句相关

1. sql内写case when函数，并把对日期类型进行转换

SELECT
	person_name,
	job_no,
	card_no,
	org_path_name,
	src_name,
	src_parent_name,
	(case when event_in_out = 1 then '入' when event_in_out = 2 then '出' end ) as
	event_in_out,
	receive_time,
	DATE_FORMAT(happen_time,'%Y-%m-%d %H:%i:%s') as happen_time
FROM
	event_acs 
WHERE
	org_id = '23'

2. 时间戳转换为年月日时分秒

其中TIMESTAMP和TAGBINDTP是表的时间戳字段

SELECT
TAGID,REGSTA_ID,REGSTA_NAME,EVENT_TYPE,from_unixtime(TIMESTAMP/1000,"%Y-%m-%d %H:%i:%s") as TIMESTAMP,from_unixtime(TAGBINDTP/1000,"%Y-%m-%d %H:%i:%s") as TAGBINDTP 
FROM
  hisio20221027140635

3. 查询当天

select 字段 from 表名 where to_days(时间字段) = to_days(now());

例如：

SELECT
  person_name,
  job_no,
  group_name,
  org_name,
  date,
  shift_name,
  (case when attendance_state = 0 then '正常' when attendance_state = 1 then '迟到' when attendance_state = 2 then '早退'when attendance_state = 3 then '旷工'end ) as
  attendance_state,
  go_work_time,
  go_work_clock_time,
  out_work_time,
  out_work_clock_time 
FROM
  event_pdms 
WHERE
 to_days(date) = to_days(now());