自用，常用sql，sql server时间格式化函数

最新推荐文章于 2025-07-14 22:28:40 发布

原创最新推荐文章于 2025-07-14 22:28:40 发布 · 588 阅读

7 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#sql #数据库 #mysql

收录于

该文章主要描述了对数据库Data_TPJ中的数据进行SQL查询，包括按设备码和时间范围统计数据量，以及筛选特定日期和地理位置的数据。

SELECT COUNT(*), DeviceCode,PaveTime
FROM Data_TPJ
WHERE PaveTime BETWEEN ‘2023-09-16%’ AND ‘2024-09-16%’
GROUP BY DeviceCode,PaveTime

SELECT DATE(PaveTime) AS 日期， COUNT(*) AS 数据量
FROM Data_TPJ
WHERE PaveTime >= ‘2023-09-16’ AND 时间字段 <= ‘2024-09-16’
GROUP BY DATE(PaveTime);
DATE_FORMAT(PaveTime,‘%Y-%m-%d’)
WHERE DeviceCode = ‘YQTP02’ AND PaveTime BETWEEN ‘2023-06-26 09:32:20’ AND ‘2023-10-22 11:27:48’

SELECT DeviceCode, FORMAT(PaveTime,‘yyyy-MM-dd’) PaveTime, COUNT(*)
FROM Data_TPJ
WHERE DeviceCode = ‘YQTP03’ AND PaveTime BETWEEN ‘2023-07-05 09:41:58’ AND ‘2023-11-20 14:10:14’
GROUP BY FORMAT(PaveTime,‘yyyy-MM-dd’),DeviceCode
ORDER BY PaveTime ASC;

SELECT *
FROM Data_TPJ
WHERE Latitude > 90 AND Longitude < 90;

SELECT COUNT(*),DeviceCode
FROM Data_TPJ
WHERE PaveTime BETWEEN ‘2023-09-16 00:00:00’ AND ‘2024-09-16 00:00:00’
GROUP BY DeviceCode;