将UNIX时间戳转换为Datatime SQL查询

发布于 2025-02-08 21:53:40 字数 316 浏览 0 评论 0原文

我有这样的查询，

select * from my_tabel where created_at >= 1655546400000

我想将此查询提供给最终用户，并且数据库中的Ceraetd_at字段是UNIX TIMESTAMP，他不知道Unix Timestamp，因此他应该能够将其更改为自定义DateTime。我想将其更改为此查询，因为此查询适用于最终用户

select * from my_tabel where created_at >= "2022-06-18 10:00:00"

原文

I have query like this

select * from my_tabel where created_at >= 1655546400000

I want to give this query to the end-user and the ceraetd_at field in the database is Unix timestamp and he doesn't know Unix timestamp so he should be able to change it to custom DateTime. I want to change it to this query because this query is readable for end-user

select * from my_tabel where created_at >= "2022-06-18 10:00:00"

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情何以堪。 2025-02-15 21:53:40

我发现这很有帮助，您可以尝试一下。转换后，您可以根据需要使用值。

DECLARE @UnixDate BIGINT = 1655546400000
SELECT CAST(DATEADD(ms, CAST(RIGHT(@UnixDate,3) AS SMALLINT), 
       DATEADD(s, @UnixDate / 1000, '1970-01-01')) AS DATETIME2(3))

有关 dateadd 函数。

在您的问题更新之后，阅读您的问题后我已经了解了什么是，您的用户想将日期与ceraetd_at字段进行比较，因为UNIX时间没有给出有关常规日期格式的太多详细信息，那就是您想要转换ceraetd_at，以便他们可以比较或运行查询。

因此，您可以这样做以下操作：

SELECT * 
FROM my_tabel 
WHERE (CAST(DATEADD(ms, CAST(RIGHT(created_at,3) AS SMALLINT), 
        DATEADD(s, created_at / 1000, '1970-01-01')) as DATETIME2(3))  
       >= '2022-06-18')

I have found this very helpful, you can try it. After converting you can use the value however you want.

DECLARE @UnixDate BIGINT = 1655546400000
SELECT CAST(DATEADD(ms, CAST(RIGHT(@UnixDate,3) AS SMALLINT), 
       DATEADD(s, @UnixDate / 1000, '1970-01-01')) AS DATETIME2(3))

More details about DATEADD function.

After the update of your question, What I have understood after reading your question is, that your user wants to compare date with ceraetd_at field, as UNIX time does not give much details about regular date format, that's you want to convert the ceraetd_at, so that they can compare or run their query.

So for doing that you can do like below:

SELECT * 
FROM my_tabel 
WHERE (CAST(DATEADD(ms, CAST(RIGHT(created_at,3) AS SMALLINT), 
        DATEADD(s, created_at / 1000, '1970-01-01')) as DATETIME2(3))  
       >= '2022-06-18')

回复收藏 0 原文

~没有更多了~