EXTRACT函数
EXTRACT 属于 SQL 的 DML(即数据库管理语言)函数,它主要用于从一个日期或时间型的字段内抽取年、月、日、时、分、秒等数据,函数返回类型为 double precision 的数值。它支持的关健字 YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、WEEKDAY、YEARDAY 等等
EXTRACT 使用语法为:
EXTRACT([关键字] from [日期])
eg:
// 从当前时间中提取年份
SELECT EXTRACT(YEAR FROM TIMESTAMP '2023-05-20 16:54:53.644833'); // 从当前时间中提取月份
SELECT EXTRACT(MONTH FROM TIMESTAMP '2023-05-20 16:54:53.644833'); // 从当前时间中提取天
SELECT EXTRACT(DAY FROM TIMESTAMP '2023-05-20 16:54:53.644833'); // 从当前时间中提取小时
SELECT EXTRACT(HOUR FROM TIMESTAMP '2023-05-20 16:54:53.644833'); // 从当前时间中提取分钟
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2023-05-20 16:54:53.644833'); // 从当前时间中提取秒
SELECT EXTRACT(SECOND FROM TIMESTAMP '2023-05-20 16:54:53.644833'); // 从当前时间中提取世纪
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2023-05-20 16:54:53.644833'); // 从当前时间中提取时间戳,单位:秒
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2023-05-20 16:54:53.644833'); 输出结果:2023 // 年
5 // 月
20 // 日
16 // 时
54 // 分
53.644833 // 秒
21 // 世纪
1684601693.644833 // 时间戳,单位:秒
加时区
select *
FROM place
JOIN event_place_r ON place.place_uuid = event_place_r.place_uuid
JOIN event ON event.event_uuid = event_place_r.event_uuid
WHERE
EXTRACT(year FROM timezone('Asia/Shanghai', event.create_time)) = '2024'
AND EXTRACT(month FROM timezone('Asia/Shanghai', event.create_time)) = '9'
AND event.event_data_source_id = '1'
sqlachemy
local_timezone: str = "Asia/Shanghai"def _to_local_time(datetime_field: sa.Column[datetime.datetime]):return func.timezone(settings.local_timezone, datetime_field)def _extract_local_time(part: str, datetime_field: sa.Column[datetime.datetime]):return func.extract(part, _to_local_time(datetime_field))stmt = (select(models.Place.place_name, models.Place.place_lng, models.Place.place_lat).select_from(models.Place).join(models.EventPlaceR).join(models.Event).where(_extract_local_time("year", models.Event.create_time) == year,_extract_local_time("month", models.Event.create_time) == month,models.Event.event_data_source_id == DATA_SOURCE_ID_12345,models.Place.place_lat != None, # noqa: E711models.Place.place_lng != None, # noqa: E711)
)
result = db_session.execute(stmt).all()