SqlServer 把Long类型日期还原yyyy-MM-dd HH:mm:ss格式日期:
SELECT CONVERT(DATETIME, Dateadd(second, 1704330940847 / 1000, '19700101 08:00'), 111) AS tt
SqlServer 把Long类型日期还原yyyy-MM-dd格式日期:
SqlServer中,按照UTC计算标准时间,时间/60/60/24计算的标准时间
SELECT(DATEADD( DAY, FLOOR( 1704330940847 / ( 24 * 60 * 60 * 1000.0 ) ), '19700101' ) )
按照name和时间来统计每个name下每天的sum值:
SELECT name,count(*) as sum,total.tt from (
SELECT name,
( DATEADD( DAY, FLOOR( AT / ( 24 * 60 * 60 * 1000.0 ) ), '19700101' ) ) as tt
FROM
test) as total GROUP BY total.name,total.tt ORDER BY total.tt desc
根据上述知识,综合使用案例:
select COUNT(*) as sum ,total.tt,total.station_id from (
SELECT
record_id,station_id,( DATEADD( DAY, FLOOR( AT / ( 24 * 60 * 60 * 1000.0 ) ), '19700101' ) ) as tt
FROM
table ) as total group by total.station_id, total.tt order by total.tt desc