How to change database timezone from UTC to local in wordpress

Question

I'm using a custom plugin in wordpress. All date and time are stored in database as UTC. I saw in posts table, there're 2 columns for UTC and local timezone (post_date, post_date_gtm), but in this plugin table, there's only 1 column to store the created date as UTC.

Currently, I'm using sql query and $wpdb to show the data between "start date" and "end date". Please help me to show them in local timezone (That is setted in general setting) instead of UTC!

Thanks and sorry about my English.

score 2 · Accepted Answer · answered Mar 29 '16 at 10:59

2

Assume your local time zone is +05:00, use the following condition:

WHERE CONVERT_TZ(col_date_utc, '+00:00', '+05:00') BETWEEN '....' AND '....'

answered Mar 29 '16 at 10:59

Dylan Su

5,975
1
16
25

Thank you! I have a small question:I use gtm_offset and it return for exp: -7.5, How can I convert this to -07:30, is there any function in wordpress support this? – mrdeeds88 Mar 30 '16 at 03:37

score 2 · Answer 2 · edited May 23 '17 at 11:45

2

I thought this might be useful

There are 3 places where the timezone might be set in MySQL: Check here link

edited May 23 '17 at 11:45

Community

1
1

answered Mar 29 '16 at 13:11

krunal sojitra

385
1
5

score 1 · Answer 3 · answered Mar 29 '16 at 11:20

1

Have you tried Admin> Settings> Timezone?

answered Mar 29 '16 at 11:20

Virendra Singh

26
3

How to change database timezone from UTC to local in wordpress

3 Answers3