将本地TIME_STAMP转换为UTC时间 [英] Convert local TIME_STAMP to UTC time

问题描述

我有两个带时间戳的表

TABLE1与当地时间TIME_STAMP

TABLE1 with TIME_STAMP in local time

TABLE2，UTC时间为TIME_STAMP

TABLE2 with TIME_STAMP in UTC

我需要做类似的事情

    select count(*) from TABLE1 where TIME_STAMP > TABLE2.TIME_STAMP

问题在于此应用将部署在多个时区，因此我无法使用以下内容

The problem is that this app will be deployed in multiple time zones so I cannot use the following

    SELECT CONVERT_TZ(TABLE2.TIME_STAMP,'EST','UTC')

因为"EST"可以是任何时区.

because 'EST' can be any time zone.

是否可以获取本地时区代码并将其替换为第二个参数? 还是有更好，更直接的方法来解决我的问题?

Is there a way of getting local time zone code and substituting it as second argument? Or is there a better and more direct approach to solving my issue?

注意:TABLE1和TABLE2 TIME_STAMPS都必须分别保留为本地和UTC，因为它们是我无法控制的外部...

NOTE: Both TABLE1 and TABLE2 TIME_STAMPS have to remain as local and UTC respectively as they are externals that I have no control over...

推荐答案

这是我实现的解决方案:

Here is the solution I implemented:

这会将NOW从本地时区转换为UTC
SELECT CONVERT_TZ( NOW(), @@session.time_zone, '+00:00' )

This will convert NOW from local timezone to UTC
SELECT CONVERT_TZ( NOW(), @@session.time_zone, '+00:00' )

这会将任何给定的TIMESTAMP字符串从本地时区转换为UTC
SELECT CONVERT_TZ( "2012-02-14 16:44:36", @@session.time_zone, '+00:00' )

This will convert any given TIMESTAMP string from local timezone to UTC
SELECT CONVERT_TZ( "2012-02-14 16:44:36", @@session.time_zone, '+00:00' )

这篇关于将本地TIME_STAMP转换为UTC时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！