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());