13

I'm currently using a shared hosting plan, and I'm not sure which version of MySQL it's using, but it does not seem to support the DATETIMEOFFSET type.

Does a version exists of MySQL that supports DATETIMEOFFSET? or are there plans for it to be implemented in the future?

CuriousGeorge
  • 7,120
  • 6
  • 42
  • 74
  • Not in the way I think you wish (as if using ```LIMIT START, OFFSET```, aye?). In MySQL you can use a ```WHERE``` clause to limit the rows from a date field within a certain period of time: ```... WHERE `date_col` BETWEEN '2015-08-18 23:03:00' AND '2015-08-19 23:03:00')``` – Avalanche Aug 19 '15 at 20:03
  • It's in the MySQL Worklogs as [WL#3744: TIME/TIMESTAMP/DATETIME with time zones](https://dev.mysql.com/worklog/task/?id=3744) but not being worked on at present. – Mark G Nov 03 '19 at 23:30
  • I'm not sure that's exactly the same thing. It looks like the timestamp/timezone implementation from PostgreSQL, which IIRC, lacks the precision to accurately represent some time zones. For example, Nepal is +5 3/4. The notes on the second page of the work log do mention this though: `WL#946 "TIME/TIMESTAMP/DATETIME with fractional seconds"`, but I can't seem to find the actual work log item for WL#946. – CuriousGeorge Nov 04 '19 at 03:18

1 Answers1

8

As far as I can tell, the DATETIMEOFFSET type is specific to Microsoft SQL Server. It is not part of the SQL standard, and is not supported by any current or past version of MySQL.

The closest equivalent to this data type in MySQL is the TIMESTAMP data type. This data type stores an absolute date and time to microsecond precision, but does not include a time zone. (The stored timestamp is always kept in UTC.)

  • That makes sense. I thought SQL was created by MS, and that the MS implementation was the reference implementation. – CuriousGeorge Aug 19 '15 at 22:17
  • 2
    I think it's wrong to recommend TIMESTAMP though(and I keep seeing this everywhere) as it cannot store a timezone or offset. – CuriousGeorge Aug 19 '15 at 22:18
  • 2
    Microsoft had nothing to do with the creation of SQL. (It was created in 1974, one year before Microsoft was even founded!) And I'm not sure why you'd consider a time zone an essential component here; `TIMESTAMP` simply represents an abstract point in time. A time zone is a sort of "format" for that point in time, and can be stored separately, if it's needed at all. –  Aug 19 '15 at 23:06
  • 3
    We need both user-perceived local time and UTC time. This is only possible if you store both a time and an offset. Storing one offset/zone for the user doesn't work either if the user uses the app while on vacation in another time zone. – CuriousGeorge Aug 19 '15 at 23:20
  • I mean that, if you need to store a time zone or UTC offset with a timestamp, you can store it as a separate column. No reason it has to be all jammed together in a single value. –  Aug 19 '15 at 23:25
  • True, but cumbersome. Also, If I wanted to do a query where the time was treated as either universal or local, I'm not sure I'd be able to do that with separate time and offset. I haven't gotten that far with this, but it seems to be possible with mssql. – CuriousGeorge Aug 19 '15 at 23:58
  • It appears I can actually do this if I want to query as universal time: `SELECT * FROM table WHERE DATE_SUB(date, INTERVAL offset SECOND)=:date` -- So although having a second column for offset is cumbersome, I suppose it's a workable solution. Thanks. – CuriousGeorge Aug 20 '15 at 03:31
  • @bitwise re:user uses the app while on vacation in another timezone - Well, in that case you'd just be storing a different offset. We usually make the client submit local offset information in the request header (assuming your working with a web app). – HK1 Jun 19 '17 at 21:23