UNIVERAL TIME - Storing and Retrieving without conversion and without changing Server Time zone

I am new to MariaDB and I am having difficulty getting around handling precision universal time. [I have in the past built large distributed systems both as services and full applications, with distributed transactions across multiple databases on multiple servers]

I wish to have a table that has a field say 'mytime' of type DATETIME. I want a flexible full a date-time range as possible, Hence DATETIME.. I wish to store values in the field in UTC Format. Note the restrictive datetime range of TIMESTAMP is not acceptable.

The data will be retrieved from many different time zones by different client software. I do not wish that the database system converts the value to either the Server or Client time zones, or truncate it to a lesser precision.

Further, I may have another field in this table, say, 'RecordUpdateTimeUTC' also of type DATETIME. I would like to automatically update this field by say using the function UTC_TIMESTAMP(6). Ideally like to specify in default to mean run UTC_TIMESTAMP(6) on the server and store in UTC Time. This does not seem possible to do so I guess I can execute INSERT INTO mytable (.., RecordUpdateTimeUTC ) VALUE (.., UTC_TIMESTAMP(6) ); but to run the UTC_TIMESTAMP(6) on the server.

I have done all the above when using MSFT SQL SERVER and OARCLE RDMS' too, however for some reason there seems to be some difficulty using MariaDB.

For me it is essential that we manage time data as precisely as possible with the greatest range covered. We also need to be efficient.


Thank you.


Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.