将数值型数据转换为yyyymmddhh24miss格式时间戳求助

场景1：SQL环境（以MySQL/PostgreSQL为例） #

源数据的整数部分正好是目标格式的14位字符，只需将数值转字符串后截取前14位即可：

-- MySQL
SELECT SUBSTRING(CAST(your_column AS CHAR), 1, 14) AS formatted_timestamp
FROM your_table;

-- PostgreSQL
SELECT SUBSTRING(your_column::text, 1, 14) AS formatted_timestamp
FROM your_table;

场景2：Python处理 #

单个数值转换 #

num_timestamp = 20220929121001.0000000
formatted = str(int(num_timestamp))
print(formatted)  # 输出：20220929121001

Pandas批量处理DataFrame #

import pandas as pd

df = pd.DataFrame({'timestamp': [20220929121001.0000000, 20220919085939.0000000]})
df['formatted_timestamp'] = df['timestamp'].astype(int).astype(str)

场景3：Java处理 #

double numTimestamp = 20220929121001.0000000;
String formatted = String.valueOf((long) numTimestamp);
System.out.println(formatted);  // 输出：20220929121001

内容的提问来源于stack exchange，提问作者BalajiAWS