Subtract 5 hours from a datetime value that is type timestamp with timezone-CodePudding

I am querying a table that has a datetime column and the value is in the format time stamp with time zone. I've tried doing a select hour(timestamp,-5) as NTime and different variants of that, but the furthest I get is an error stating the timestamp is not a valid name/type. I'm officially going off the deep end on this....

Basically, I just need the new alias column values to be a time stamp that is 5 hours behind the timestamp that is in the original table. Thank you all in advance!!

CodePudding user response：

MariaDB / MySQL doesn't have a "timestamp with timezone" data type.

DATETIMEs are simple wall time, and TIMESTAMPs are UNIX time_t timestamps (the number of seconds since 1970-01-01T00:00:00UTC).

You can convert DATETIME values from one time zone to another by with tz_convert().

SELECT tz_convert('2022-04-08 21:53', 'America/Chicago', 'UTC')

for example.

Or, just to do date arithmetic you can do stuff like this.

SELECT '2022-04-08 21:53' - INTERVAL 5 HOUR