一、mysql数据库下

1.精确到秒

1）12小时制

select FROM_UNIXTIME(1676533527,'%Y-%m-%d %h:%i:%s') as date
select FROM_UNIXTIME(1676533527000/1000,'%Y-%m-%d %h:%i:%s') as date

2）24小时制

select FROM_UNIXTIME(1676533527,'%Y-%m-%d %H:%i:%s') as date
select FROM_UNIXTIME(1676533527000/1000,'%Y-%m-%d %H:%i:%s') as date

2.精确到日期

select FROM_UNIXTIME(1676533527,'%Y-%m-%d')
select FROM_UNIXTIME(1676533527000/1000,'%Y-%m-%d')
select DATE(NOW());
SELECT SUBSTR(NOW(),1,10)

3.customer_event表中提取当前日期数据

SELECT
    * 
FROM
    customer_event 
WHERE
    FROM_UNIXTIME( field_date / 1000, '%Y-%m-%d' ) = SUBSTR(
        NOW(),
        1,
    10)

二、kudu数据库,需要先将数据转换成string

1.转换成日期

select SUBSTR(CAST(now() AS string),1,10);

2.转化成时间

select CAST(now() AS string);

3.customer_event表中提取当前日期数据

-- 小白总结，这一条写的有点儿绕，如果大家有简洁的方法，欢迎评论区给出
SELECT
 COUNT(1) 
FROM
 customer_event
WHERE
 SUBSTR(CAST(from_unixtime(CAST((field_date+8*3600*1000) / 1000 AS int)) as string),1,10) = SUBSTR(CAST(NOW() AS STRING), 1, 10)

4.更新，找技术同学写了一条取昨天事件的

select count(1) from customer_event where tenant_id = 1 and field_date >= (unix_timestamp(to_date(now()))*1000-115200000) and field_date < (unix_timestamp(to_date(now()))*1000-28800000) and event="c_latest_pur_event";


select count(1) from customer_event where tenant_id = 1 and field_date between (unix_timestamp(to_date(now()))*1000-115200000) and (unix_timestamp(to_date(now()))*1000-28800000)) and event="c_latest_pur_event";

5.更新，取今天的事件

select count(1) from customer_event where tenant_id = 1 and field_date >= (unix_timestamp(to_date(now()))*1000-28800000) and field_date < (unix_timestamp(to_date(now()))*1000+57600000) and event="c_latest_pur_event";


select count(1) from customer_event where tenant_id = 1 and field_date between (unix_timestamp(to_date(now()))*1000-28800000) 
and (unix_timestamp(to_date(now()))*1000+57600000)) and event="c_latest_pur_event";