generate timezone specific date and time

Question

My mysql query is as follows:

select * from orders

where orders has created_at field.

Now what i want is i want to convert created_at to 7 am of next day of created_at date . and the time created should be according to local timezone.

Is there a way to achieve it?

Note:: and orders table also has field timezone which has values like Australia/Sydney , Asia/Kolkata etc.

See: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Nov 05 '19 at 11:36
Pretty sure when you read [Should MySQL have its timezone set to UTC?](https://stackoverflow.com/questions/19023978/should-mysql-have-its-timezone-set-to-utc) solves your question or a great part of it.. .. — Raymond Nijland, Nov 05 '19 at 12:01
@RaymondNijland I am not talking about mysql timezone. that is why i mentioned timezone field in the note section of the question — Mrugesh, Nov 05 '19 at 12:11
*"I am not talking about mysql timezone. that is why i mentioned timezone field in the note section of the question "* Did you read the complete answer as you missed the main function from that link as `CONVERT_TZ()` is used in there which can be used with table data and use table stored timezones -> *"Note:: and orders table also has field timezone which has values like Australia/Sydney , Asia/Kolkata etc."*.. — Raymond Nijland, Nov 05 '19 at 12:21
See manual about using [CONVERT_TZ()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_convert-tz) -> *" Note To use named time zones such as 'MET' or 'Europe/Amsterdam', the time zone tables must be properly set up. For instructions, see [Section 5.1.13, MySQL Server Time Zone Support](https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html)"* — Raymond Nijland, Nov 05 '19 at 12:21

score 1 · Answer 1 · answered Nov 05 '19 at 12:31

You need to go through the CONVERT_TZ function

// syntax
CONVERT_TZ(your_timestamp_column_name, 'db_timezone', 'your_desired_timezone_name')

// Example
SELECT CONVERT_TZ(`created_at`, 'UTC', `timezone`) as `my_date` FROM orders

Now when you have a date in your desired timezone, you can add hour and time that comes from a difference of next day 7AM and .my_date

generate timezone specific date and time

1 Answers1