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

Question

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

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.

Thanks and best regards.

Answer 1

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.

without resorting to some MySQL functions

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)

Answer 2

MySQL always store timestamps as utc. Dates are always stored without timezone information.

You can configure mysql to return values from now() in different timezones.

To store the current offset you need to add this to some column on your own.