sql将毫秒数字转换为日期

sql将毫秒数字转换为日期

例如某个字段的毫秒数为:1345626941701

转换函数:select dateadd(s, 1345626941701/1000 ,'1970-1-1 08:00:00')

即用 毫秒数/1000 即可。

对于字段:col/1000再用上面的函数即可

对于oracle麻烦点:

select to_date('1970-01-01 08:00:00','yyyy-MM-dd HH24:MI:SS')+(1345626941701/86400000 - 1/86400) from dual;

------------

在开发过程中,为了方便,经常将时间日期的毫秒数以整形的格式存到数据库中,虽然方便了不同地方的处理,但也会增加些麻烦。比如,我要到数据库里直接看某个时间的值,都是一长串的数字,鬼都看不懂,想到看到该字段的值,还要写个转换程序,把毫秒数在转换成日期时间类型,或者拿个计算器来算。要查看的记录少,还能将就,如果多了,那不崩溃才怪呢。于是想到了在SQL里直接转换的方法,还好牛人多,在网上搜了一下,还真有相关的东西,原始的语句如下:

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') CURRENT_DATE, (SYSDATE- TO_DATE('1970-01-01','YYYY-MM-DD')) * 86400000 CURRENT_MILLI FROM DUAL;

这只是个例子,还不能直接解决上面的问题,所有就改了一下,是以自定义函数的形式存在的,代码如下:

CREATE OR REPLACE FUNCTION DATELONG2TEXT (PARAM IN LONG) RETURN VARCHAR2 AS
V_TEXT VARCHAR2(64);
BEGIN
SELECT TO_CHAR(TO_DATE('1970-01-01', 'YYYY-MM-DD')+(PARAM/(24*60*60*1000) + 8/24 - 1/86400),'YYYY-MM-DD HH24:MI:SS') INTO V_TEXT FROM DUAL;
RETURN V_TEXT;
END DATELONG2TEXT;

使用示例:

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),SYSDATE,DATELONG2TEXT((SYSDATE - 8 / 24 - TO_DATE('1970-01-01', 'YYYY-MM-DD')) * 86400000) FROM DUAL;

说明:

1、加上 8/24 ,是因为时间格式是GMT,结果会受时区的影响,我们在东8区,所以要加上8个小时 ;

2、减1/86400,至于为什么,我也说不清楚,只是在实际当中确实多了1秒,只好以这种方式减去了,根据时间情况确定是否进行调整。

当然,我们也可以类似的写出将日期由字符串转换成毫秒数的函数。

<think>我们已知用户需要将特定日期字符串'20250701'转换毫秒(即从1970-01-01 00:00:00 UTC到该日期毫秒数) 注意:用户给出的日期是'20250701',格式为YYYYMMDD,没有时间部分,因此默认时间为00:00:00。 由于不同数据库的日期转换函数不同,我们需要分数据库讨论。 根据引用[1]和[2]中的信息,我们知道在Oracle中没有直接获取毫秒的函数,并且日期计算需要注意进制问题(日期是60进制,而数计算是10进制)。 因此,我们需要确保在计算时考虑日期的正确转换。 以下分别针对不同数据库提供解决方案: 1. MySQL/MariaDB: 我们可以先将字符串转换日期,然后使用UNIX_TIMESTAMP函数获取秒级时间戳,再乘以1000得到毫秒。但是注意,UNIX_TIMESTAMP函数默认返回的是从'1970-01-01 00:00:00' UTC到给定日期的秒数(忽略毫秒部分)。 由于这里没有时间部分,所以可以直接转换。 步骤: a. 将字符串'20250701'转换日期格式:STR_TO_DATE('20250701','%Y%m%d') b. 使用UNIX_TIMESTAMP获取秒数:UNIX_TIMESTAMP(STR_TO_DATE('20250701','%Y%m%d')) c. 乘以1000得到毫秒(注意:如果日期在1970年之前,UNIX_TIMESTAMP返回0,但这里日期是2025年,所以没问题) 但是,MySQL的UNIX_TIMESTAMP函数对日期范围有限制(1970-2038),2025年在范围内。 因此,SQL语句如下: SELECT UNIX_TIMESTAMP(STR_TO_DATE('20250701','%Y%m%d')) * 1000 AS timestamp_ms; 2. Oracle: 根据引用[1]中的提示,我们可以计算两个日期时间差(以天为单位),然后乘以一天的毫秒数(24*60*60*1000=86400000)来得到毫秒。 但是需要注意:Oracle的日期相减得到的是天数(带小数部分表示时分秒),因此我们需要将天数乘以86400000。 步骤: a. 将字符串转换日期:TO_DATE('20250701','YYYYMMDD') b. 减去1970-01-01(注意:1970-01-01在Oracle中默认是0时区,但这里我们假设用户日期是本地时区,如果要求UTC,则需考虑时区转换) c. 计算毫秒:由于日期没有时间部分,所以相减后得到的是整数天(从1970-01-01到2025-07-01的天数)?实际上,我们需要精确到毫秒,所以即使没有时间部分,我们也可以直接计算天数然后乘以毫秒数,但这样得到的是该日期的0点(本地时间)对应的毫秒数。 注意:在Oracle中,日期相减的单位是天,所以: (TO_DATE('20250701','YYYYMMDD') - TO_DATE('19700101','YYYYMMDD')) * 24 * 60 * 60 * 1000 但是,这样计算得到的是从1970-01-01 00:00:00(本地时区)到2025-07-01 00:00:00(本地时区)的毫秒数。如果我们需要UTC时间,则要处理时区问题。 由于用户没有指定时区,我们假设用户使用本地时区(即数据库服务器的时区)与UTC无关。如果要求UTC,则需要将日期转换为UTC时间。 这里我们按本地时区计算(不考虑时区转换): SELECT (TO_DATE('20250701','YYYYMMDD') - TO_DATE('19700101','YYYYMMDD')) * 24 * 60 * 60 * 1000 AS timestamp_ms FROM DUAL; 但是,注意:Oracle的日期类型不包含时区信息,所以上述计算依赖于当前会话的时区设置。如果需要精确的UTC毫秒,应该使用带时区的类型(TIMESTAMP WITH TIME ZONE)并转换为UTC。 考虑时区的版本(将日期视为本地时区,然后转换为UTC): SELECT (CAST(TO_TIMESTAMP_TZ('20250701 00:00:00 ' || SESSIONTIMEZONE, 'YYYYMMDD HH24:MI:SS TZR') AT TIME ZONE 'UTC' AS TIMESTAMP) - TIMESTAMP '1970-01-01 00:00:00 UTC') * 24 * 60 * 60 * 1000 FROM DUAL; 但这样比较复杂。如果用户不需要精确到UTC,则用第一种简单方法。 3. SQL Server: 使用DATEDIFF函数,注意DATEDIFF函数返回的是两个日期之间的时间差(整数),但单位可以是毫秒。不过,DATEDIFF函数返回的是int类型,对于大日期可能溢出,所以SQL Server 2016+可以使用DATEDIFF_BIG。 SELECT DATEDIFF_BIG(MILLISECOND, '1970-01-01', CONVERT(DATETIME, '20250701', 112)) AS timestamp_ms; 其中,112是格式代码(YYYYMMDD)。 如果版本低于2016,则使用DATEDIFF,但要注意溢出问题(2025年不会溢出,因为最大可以表示到2038年,但毫秒数很大,所以最好用DATEDIFF_BIG)。 4. PostgreSQL: SELECT EXTRACT(EPOCH FROM TO_TIMESTAMP('20250701','YYYYMMDD')) * 1000 AS timestamp_ms; 注意:以上所有方法都假设输入日期是本地时区(数据库服务器时区)的日期,并且不考虑夏令时。如果要求UTC毫秒,则需要在日期转换时指定时区。 由于用户没有明确要求时区,我们默认使用本地时区(即数据库设置的时区)进行计算。 但是,用户给出的日期是'20250701',没有时间部分,所以转换后的毫秒表示该日期0点0分0秒(本地时间)到1970-01-01 00:00:00 UTC的毫秒数?实际上,这取决于数据库如何处理时区。 为了通用性,我们建议用户明确时区要求。如果要求UTC时间,则应该将日期字符串加上时间(00:00:00)和时区(比如+00:00)转换为UTC时间。 鉴于问题复杂,这里给出各数据库本地时区(即不考虑时区转换)的解决方案,并提醒用户注意时区。 总结各数据库的SQL语句(假设本地时区): MySQL/MariaDB: SELECT UNIX_TIMESTAMP(STR_TO_DATE('20250701','%Y%m%d')) * 1000; Oracle(简单方法,本地时区): SELECT (TO_DATE('20250701','YYYYMMDD') - TO_DATE('19700101','YYYYMMDD')) * 24 * 60 * 60 * 1000 FROM DUAL; SQL Server(使用DATEDIFF_BIG): SELECT DATEDIFF_BIG(MILLISECOND, '1970-01-01', CONVERT(DATETIME, '20250701', 112)); PostgreSQL: SELECT EXTRACT(EPOCH FROM TO_TIMESTAMP('20250701','YYYYMMDD')) * 1000; 注意:以上SQL在各自数据库中运行即可得到毫秒。 但是,由于Oracle中日期相减得到的是天数,而天数乘以86400000(24*60*60*1000)得到毫秒数,这个计算是准确的(因为一天固定86400秒,不考虑闰秒)。 另外,用户提到“Oracle没有对应函数”,所以我们在Oracle中使用了直接计算。 最后,提醒用户:如果数据库时区不是UTC,那么同样的日期字符串在不同的数据库时区下计算出来的毫秒可能不同(因为本地0点对应UTC的时间不同)。如果要求UTC时间,则需要在转换时指定时区。 因此,我们建议用户根据实际使用的数据库选择对应的SQL,并注意时区问题。</think>在 SQL 中将日期 `20250701` 转换毫秒(Unix 时间戳),需要根据不同数据库系统采用不同的方法。以下是主流数据库的实现方案: ### 1. MySQL/MariaDB ```sql SELECT UNIX_TIMESTAMP(STR_TO_DATE('20250701', '%Y%m%d')) * 1000 AS timestamp_ms; ``` **说明**: 1. `STR_TO_DATE('20250701', '%Y%m%d')` 将字符串转换日期类型 2. `UNIX_TIMESTAMP()` 获取秒级时间戳 3. `* 1000` 转换毫秒 4. 结果示例:`2025-07-01 00:00:00` → `1751328000000` ### 2. Oracle ```sql SELECT (TO_DATE('20250701', 'YYYYMMDD') - TO_DATE('19700101', 'YYYYMMDD')) * 86400000 AS timestamp_ms FROM DUAL; ``` **说明**: 1. `TO_DATE()` 将字符串日期 2. 日期相减得到天数差(Oracle日期运算单位为天) 3. `86400000 = 24*60*60*1000` 将天数毫秒 4. 需确保会话时区一致[^1][^2] ### 3. SQL Server (2016+) ```sql SELECT DATEDIFF_BIG(MILLISECOND, '1970-01-01', CONVERT(DATETIME2, '20250701', 112)) AS timestamp_ms; ``` **说明**: 1. `CONVERT(..., 112)` 按格式 `YYYYMMDD` 解析日期 2. `DATEDIFF_BIG()` 精确计算毫秒差(避免整数溢出) ### 4. PostgreSQL ```sql SELECT EXTRACT(EPOCH FROM TO_TIMESTAMP('20250701', 'YYYYMMDD')) * 1000 AS timestamp_ms; ``` ### ⚠️ 关键注意事项 1. **时区问题**: - 所有方法默认使用数据库会话时区 - 若需UTC时间,需显式转换时区(如Oracle添加 `AT TIME ZONE 'UTC'`) 2. **精度保证**: - 输入日期时间部分时,结果表示当日 `00:00:00` 的毫秒 - 若需包含时间(如 `20250701 12:30:45`),需调整日期格式和计算逻辑[^2] 3. **数据类型**: - 输出为BIGINT类型(防止溢出) - 日期字符串需严格匹配格式(如 `YYYYMMDD` 必须8位数字) > 验证工具:使用 [Unix时间转换器](https://www.epochconverter.com) 在线验证结果,输入 `2025-07-01` 应得 `1751328000000`[^1][^2]。 --- ###
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值