是否有与Oracle的TIMESTAMP WITH TIME ZONE等效的MySQL? [英] Is there MySQL equivalent to Oracle's TIMESTAMP WITH TIME ZONE?

问题描述

是否存在与Oracle TIMESTAMP WITH TIME ZONE等效的MySQL?

Is there MySQL equivalent to Oracle's TIMESTAMP WITH TIME ZONE?

我需要将包含具有该数据类型的某些列的Oracle表映射到MySQL表中，但是我似乎找不到不借助某些MySQL函数的简便方法.

I need to map a Oracle table, which has some columns with that datatype, into a MySQL table but I can't seem to find an easy way to do this without resorting to some MySQL functions.

感谢和问候.

推荐答案

否，您需要将数据分为两列，一列为日期时间，另一列为时区信息.但是，您在后一个字段中输入的内容取决于存储在Oracle中的内容-TIMESTAMP WITH TIME ZONE数据类型可以包含TZ偏移量和(可选)时区区域.显然，后者是要求日期时间在语义上正确的要求，但IIRC Oracle不会强制填充此数据.

No, you'll need to split the data into 2 columns, one a datetime, and the other holding the timezone information. But what you put in the latter field is dependant on what you've got stored in Oracle - the TIMESTAMP WITH TIME ZONE Datatype can contain the TZ offset and (optionally) the time zone region. Obviously the latter is a requirement for the date time to be semantically correct, but IIRC Oracle does not enforce this data being populated.

不使用某些MySQL函数

without resorting to some MySQL functions

由于MySQL没有数据类型，因此很难编写MySQL函数来处理它-在支持数据类型的Oracle中创建与MySQL兼容的表示要容易得多.您只需要计算出实际获得的数据，然后决定如何在MySQL中表示它即可.按照惯例，这意味着将其与TZ一起存储在UTC中，并在单独的列中进行存储，然后根据需要使用convert_tz函数对其进行转换(始终来自UTC)

Since MySQL doesn't have the datatype, it'll be very difficult to write MySQL function to process it - it's a lot simpler to create a MySQL compatible representation in Oracle where the datatype is supported. You just need to work out what data you've actually got and decide how you want to represent it in MySQL. By convention that means storing it in UTC along with the TZ in a seperate column, then convert it on selection with the convert_tz function (always from UTC)

这篇关于是否有与Oracle的TIMESTAMP WITH TIME ZONE等效的MySQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！