PostgreSQL EXTRACT 日期函数

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: E711
        models.Place.place_lng != None,  # noqa: E711
    )
)
result = db_session.execute(stmt).all()