如何使用 MySQL 的 CONVERT_TZ() 获取每天的最后一个条目

发布于 2024-11-25 04:07:39 字数 571 浏览 1 评论 0原文

因此，我使用此代码从数据库中获取每天的最后一个条目。

SELECT a.id, a.userid, a.jurisdiction, a.country, a.date
FROM dk_location_records AS a,
        (SELECT userid, DATE(date) AS just_date, MAX(date) AS date
            FROM dk_location_records
            GROUP BY 1, 2 --userid, DATE(date)
        ) AS b
WHERE a.userid = b.userid
AND a.date = b.date;

我的问题是，如何合并类似以下内容的内容： CONVERT_TZ( date, '+00:00', '+01:00' ) 来获取不同时区每天的最后一个条目。到目前为止，我已经成功地使用 CONVERT_TZ 来简单地显示转换后的日期。但是，您可以同时使用 MAX() 和 CONVERT_TZ() 吗？

原文

So I'm using this code to get the last entry for each day from a database.

SELECT a.id, a.userid, a.jurisdiction, a.country, a.date
FROM dk_location_records AS a,
        (SELECT userid, DATE(date) AS just_date, MAX(date) AS date
            FROM dk_location_records
            GROUP BY 1, 2 --userid, DATE(date)
        ) AS b
WHERE a.userid = b.userid
AND a.date = b.date;

My question is, how can I incorporate something like: CONVERT_TZ( date, '+00:00', '+01:00' ) to get the last entry per day for a different timezone. I've so far managed to use the CONVERT_TZ to simply display the converted dates. However, can you use MAX() and CONVERT_TZ() together?

分享到QQ

分享到微博

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

发布评论

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

蘸点软妹酱 2024-12-02 04:07:39

如果您想极端地选择单行，您可以通过按所需条件对结果集进行排序并将输出限制为单行来尝试。也许，

SELECT a.id, a.userid, a.jurisdiction, a.country, a.date
ORDER BY CONVERT_TZ( date, '+00:00', '+01:00') DESC
LIMIT 1

它应该比执行子查询表扫描相当快。

不过，我可能会误解你在这里想要做什么。转换时区不会改变日期的顺序。如果你想搜索其他时区一天内的条目，可以尝试

SELECT a.id, a.userid, a.jurisdiction, a.country, a.date
WHERE CAST(CONVERT_TZ(a.date, '+00:00', '+01:00') AS DATE) = CURDATE()
ORDER BY a.date DESC
LIMIT 1

当然，修改'+01:00'（目标时区）和CURDATE()（您正在搜索的日期）以满足您的需求。

If you want to select a single row in an extreme, you can try it by ordering the resultset by the desired criteria and limiting the output to a single row. Something, perhaps, like

SELECT a.id, a.userid, a.jurisdiction, a.country, a.date
ORDER BY CONVERT_TZ( date, '+00:00', '+01:00') DESC
LIMIT 1

It should be fairly faster than doing a subquery table scan.

Although, I may be misunderstanding what you're trying to do here. Converting the timezone won't change the order of the dates. If you'd like to search for entries within a day in another timezone, it's possible to try

SELECT a.id, a.userid, a.jurisdiction, a.country, a.date
WHERE CAST(CONVERT_TZ(a.date, '+00:00', '+01:00') AS DATE) = CURDATE()
ORDER BY a.date DESC
LIMIT 1

Of course, modify '+01:00' (destination timezone) and CURDATE() (date you're searching for) to fit your needs.

回复收藏 0 原文

~没有更多了~