Stack Overflow
Stack Overflow

Questions tagged [convert-tz]

Convert from one timezone to another (MySQL)

Convert from one timezone to another (MySQL)

Format

CONVERT_TZ(dt,from_tz,to_tz)

Examples

SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');

References

79 questions
1
vote
0 answers

How to use mySQL convert_tz functions in Laravel/Lumen query builder

$result['tests'] = Test::select('test.test_id', DB::raw('convert_tz(test.updated_at,+00:00,-05:30) as updated_at'), 'test.created_at') ->orderBy('test.test_id', 'ASC') ->get()->toArray(); I already find the solution for CASE WHEN…
Sobin Augustine
  • 3,639
  • 2
  • 25
  • 43
1
vote
0 answers

How to convert all datetimes to +00:00 depending on their timezones in MySQL?

I have a bunch of datetime rows in MySQL like: Birthday 422859600000 418705200000 8132400000 869886000000 422859600000 If I convert this datetimes to the format yyy-mm-dd hh:mm:ss.0000 I get this: 1983-05-27 00:00:00.0000 1983-04-08 22:00:00.0000…
Desarrollo Desafio de Guerrero
  • 129
  • 1
  • 2
  • 12
1
vote
0 answers

Convert MySQL date and time columns from CST to GMT+2

I have two columns in a MySQL database table, which already had data in CST time format. I want to convert those data from CST to GMT+2 (update current data). Here's what I did using "CONVERT_TZ()" function as in this reference:…
CairoCoder
  • 3,091
  • 11
  • 46
  • 68
1
vote
2 answers

Timzone conversions on date only and time only - is it necessary?

We've been working on implementing timezone support for our Web app. This great SO post has helped us a bunch: Daylight saving time and time zone best practices We've implelmented the OLSON TZ database in MYSQL and are using that for TZ…
salonMonsters
  • 1,237
  • 3
  • 16
  • 26
1
vote
0 answers

MySql CONVERT_TZ not working on time conversion day

I try to convert timezones from local format to UTC that works fine except in one case. At the day of conversion (27.03 02:00:00) and only when going from GMT+1 to GMT+2. (The conversion back is at 30.10 at 03:00:00) OK -> SELECT…
true_gler
  • 544
  • 6
  • 23
1
vote
1 answer

Why does switching the order of two time zones that are the same time in mysql convert_tz make a difference?

SELECT CONVERT_TZ('2020-06-30 23:59:59','America/Caracas','US/Eastern'); This returns '2020-07-01 00:29:59' which is strange because EST and Venezuela actually share the same time. SELECT CONVERT_TZ('2020-06-30…
user6655061
  • 92
  • 1
  • 7
1
vote
1 answer

MySQL default time format UTC or GMT?

Hi I'm so confused with this UTC and GMT I'm inserting in MySQL table as ex column "event_date" like "2010-07-01 23:50:00" (datetime) my client asking an option in front end as GMT +1 GMT +2 GMT -1 GMT -2 ...etc if I select any option GMT +2 the…
Gobi
  • 291
  • 1
  • 5
  • 17
1
vote
1 answer

Mysql CONVERT_TZ returns null when using time_zone_name

I am hosting a website on a shared server. They very helpfully (not) are refusing to set up the timezone tables. Not to be undone, I set up my own using the documentation (both ways) 1) via the SQL and 2 via the tables In both cases all the tables…
MaxSunshine
  • 11
  • 3
1
vote
1 answer

MySQL getting time in specific offset

SELECT (from_time user_offset) as start FROM `availabilities`; I am trying to add current logged in user's timezone offset to the time column values I am fetching. The value of from_time will be like 02:30:00 and value of offset will like…
Deepanshu Goyal
  • 2,738
  • 3
  • 34
  • 61
1
vote
1 answer

Bulk converting eastern timezone to gmt using CONVERT_TZ 12k+ records

UPDATE bridge_copy SET game_begin = CONVERT_TZ(game_begin,'EST','GMT'), game_end = CONVERT_TZ(game_end,'EST','GMT'); This is the query I attempted to run, and it failed misserably. What the end result was, was 2 columns filled with…
chris
  • 36,115
  • 52
  • 143
  • 252
1
vote
1 answer

MySQL displaying wrong time for some timezones

I am using MySQL 5.1.34 community server. I have taken the MySQL timezone description tables from here. Now for some timezones the time displayed by MySQL is wrong, because of wrong "day light saving" calculation for these time zones. Ex: The query…
Varun
  • 4,054
  • 6
  • 31
  • 54
1
vote
1 answer

How to import the correct datetime from mysql to solr?

I don't figure out how to import the correct datetime from mysql to solr via the DataImportHandler. After the import the datetime values get substracted 2 hours mysql "created_at 2013-04-05 15:04:21" gets in solr to…
domfry
  • 11
  • 2
1
vote
2 answers

my sql query for last 7 days data?if data not there then even fetch date and shows with 0 or null

i have made one demo Enity in sql Fidldle. my table have following collumn displayId | displaytime | campaignId now what i want is to select the last 7 days entry for the particular campaignId (i mean where campaignId="") there should be multiple…
BhavikKama
  • 8,566
  • 12
  • 94
  • 164
1
vote
1 answer

mysql Query Timezone conversion

I have show times stored in all in Eastern and ID from timezone settings table. Timezone settings table has Timezone_id, GMT offset and the timezone Name. GMT offset are stored like +6, -4, +3.5, -4.5, etc. I am trying to write a query to pull the…
Kevin Rave
  • 13,876
  • 35
  • 109
  • 173
0
votes
1 answer

Why does MySQL CONVERT_TZ alter the seconds after timezone adjustment?

I'm storing UTC datetime values in MySQL. I use CONVERT_TZ to handle timezone conversion to query/save local datetimes to/from UTC in the database. Upon testing I noticed this strange peculiarity in how the conversion works. Can anyone explain why…
spoulson
  • 21,335
  • 15
  • 77
  • 102
1 2
3
4 5 6