python与sql：时间格式转换函数

在交通数据分析之中，存在很多时间格式转换的操作，比如时间格式转为字符串格式、时间格式转为时间戳、提取出小时、分钟等。笔者日常工作中需要在python、clickhouse、postgresql中进行相关操作，下面介绍一下相关操作的代码实现。

欢迎关注本人公众号--交通数据探索师

1、字符串转时间格式

比如对于字符串'2024-01-01-12:28:30'转为时间格式

python

import datetime demo_time = '2024-01-01 12:28:30' # python中主要用到datetime模块中的strptime()函数, 用于将字符串转换成时间格式 # 函数原型：datetime.datetime.strptime(string, format) # string: 待解析的字符串 # format: 解析的格式 demo_datetime = datetime.datetime.strptime(demo_time, '%Y-%m-%d %H:%M:%S') print(f'demo_datetime的类型为: {type(demo_datetime)}') print(f'demo_datetime的值为: {demo_datetime}')

clickhouse

-- clickhouse中parseDateTimeBestEffort等同于python中datetime.datetime.strptime()函数， -- 该函数会自动解析字符串时间，不用提供时间格式 select parseDateTimeBestEffort('2024-01-01-12:28:30') -- 输出为2024-01-01-12:28:30

postgresql

-- postgresql中，to_timestamp函数等同于python中datetime.datetime.strptime()函数 select to_timestamp('2024-01-01-12:28:30', 'YYYY-MM-DD HH24:MI:SS') -- 输出为2024-01-01-12:28:30

2、时间格式转字符串

python

import datetime # 先把字符串转为时间格式 demo_time = datetime.datetime.strptime('2024-01-01 12:28:30', '%Y-%m-%d %H:%M:%S') # python中主要用到datetime模块中的strftime()函数, 用于将时间格式化成字符串 # 函数原型：datetime.datetime.strftime(datetime, format) # datetime: 时间格式 # format: 格式化字符串 demo_datetime = datetime.datetime.strftime(demo_time, '%Y%m%d %H%M%S') print(f'demo_datetime的类型为: {type(demo_datetime)}') print(f'demo_datetime的值为: {demo_datetime}')

clickhouse

-- clickhouse中formatDateTime等同于python中datetime.datetime.strftime()函数 select formatDateTime(toDateTime('2024-01-01 12:28:30'), '%Y%m%d') -- 输出为'20240101'

postgresql

-- postgresql中，to_char函数等同于python中datetime.datetime.strftime()函数 SELECT to_char(to_timestamp('2024-01-01 12:28:30', 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24') -- 输出为'2024-01-01 12'

3、时间格式转时间戳

常见的是10位时间戳，单位是秒，13位时间戳单位是毫秒

python

import time import datetime demo_time = '2020-04-08 08:06:40' demo_datetime = datetime.datetime.strptime(demo_time, '%Y-%m-%d %H:%M:%S') # 转为时间戳 ts = int(datetime_obj.timestamp()) print(f'ts: {ts}') -- 输出为：ts: 1586304400

clickhouse

-- 接收字符串形式 select toUnixTimestamp('2020-04-08 08:06:40') --输出为1586304400 -- 接收时间格式 select toUnixTimestamp(parseDateTimeBestEffort('2020-04-08 08:06:40')) --输出为1586304400

postgresql

select extract(epoch from timestamp '2020-04-08 08:06:40') -- 输出1586333200 -- 对于该函数输出的时间戳, 我们看出它是和python转出来的是不一样的，两者相减再除以3600 -- 我们可以发现正好差了8个小时，这对应的是8个小时时差，我们只需要再减去8个小时时差对应的秒数即可 -- 注意，这里要接收字符串格式 select extract(epoch from timestamp '2020-04-08 08:06:40') - 8*3600 --输出为1586304400

4、时间戳转时间格式

python

import datetime # 10位时间戳，必须是数值形式 timestamp_10 = 1586304400 # 13位时间戳 其转时间格式时，要先除以1000转为秒为单位的10位时间戳 timestamp_13 = 1586304400000 # 时间戳转时间，转换出来的dt_10和dt_13是时间格式，如果想转换为字符格式，可以参照下面 dt_10 = datetime.datetime.fromtimestamp(timestamp_10) dt_13 = datetime.datetime.fromtimestamp(timestamp_13/1000) # 再转为字符格式 dt_10_str = datetime.datetime.fromtimestamp(timestamp_10).strftime('%Y-%m-%d') print(f'dt_10: {dt_10}') print(f'dt_13: {dt_13}') print(f'dt_10_str: {dt_10_str}')

clickhouse

select toDateTime(1586304400) --输出为2020-04-08 08:06:40

postgresql

select to_timestamp(1586304400) --输出为2020-04-08 08:06:40