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
0
votes
1 answer

Mysql Convert_tz

I'm trying to convert a datetime from Asia/Manila to EST timezone without declaring the exact interval like date_sub(), subdate(), date_add(), adddate() i find it easy to use SELECT DATE_SUB('2016-04-04 13:00:00', INTERVAL 12 HOUR); the result will…
user1697839
  • 81
  • 7
0
votes
1 answer

CONVERT_TZ function not working: Time zone data for MySQL

I am trying to use the CONVERT_TZ in MySQL. From what i have looked up the time_zone tables in the mysql schema need to be populated. I have looked this up on many different site and followed a few tutorials including this tutorial but the tables…
Hip Hip Array
  • 4,665
  • 11
  • 49
  • 80
0
votes
4 answers

SQL time issue - need it 6 hours forward

Possible Duplicate: Add 2 hours to current time in MySQL? My server is currently based on us eastern time, and as I am in the UK, I need to move this forward 6 hours. When using NOW() in mysql, i am getting the wrong time, is there anything I can…
sark9012
  • 5,485
  • 18
  • 61
  • 99
0
votes
0 answers

CONVERT_TZ('2015-08-03 21:00:00', 'GMT','Americia/Chicago') displaying one hour ahead in day light saving period

I am trying to list the recurring events from google calendar. the sync successfully done but while listing the info from my local database (saved as gmt date time) using The mysql convert_tz() I am getting the time one hour ahead while in day…
Sachinvias
  • 33
  • 4
0
votes
0 answers

convert timezone returning null

I have a query that contains CONVERT_TZ(NOW(),@@global.time_zone, 'Europe/Madrid') as the value for an insert statement. This has worked fine on several servers. Now, on a new one, that returns null causing an error. What can be the cause?
matteo
  • 2,934
  • 7
  • 44
  • 59
0
votes
1 answer

MySQL CONVERT_TZ doesn't work for 2 same conversions

I got a table with event time logs, and I need to convert 2 columns to a common timezone. So if I do (convert to different timezones) SELECT id ,CONVERT_TZ(starting_time, 'UTC', 'Europe/London') AS starting_time …
Pedro Montoto García
  • 1,672
  • 2
  • 18
  • 38
0
votes
1 answer

How do I run my query and add the convert_tz(FROM (FROM_UNIXTIME

This will be simple for you. I have a query that returns all the information in the table but the dates are all Unix timestamp and I need to convert that one column. These are my 2 strings and I just need to know how to link them together: Query…
user3003638
  • 1
  • 3
0
votes
1 answer

CONVERT_TZ in MySQL

How exactly do I use this with the following code to counteract DST with +1hr: SELECT datetime FROM events WHERE datetime > NOW() GROUP BY datetime ORDER BY datetime I can't quite work out how I do this from the various examples I've seen on…
user2505513
  • 343
  • 4
  • 9
  • 24
0
votes
2 answers

Preparing a Prepare statement in mysql and using convert_tz in it

I have written a prepare statement like this and is working fine. BEGIN SET @tempaccountID :=CONCAT('\'',accountID1,'\''); SET @tempdeviceID := CONCAT('\'',deviceID1,'\''); SET @query :=CONCAT('select accountID, deviceID, …
writeToBhuwan
  • 3,233
  • 11
  • 39
  • 67
0
votes
2 answers

how to convert given offset time(in seconds) to Format like +hh:mm in java?

i want to give support of timezone..for that am storing the value of date in the database with the UTC Time and then i am converting the time depends on the user local time zone..for that am using this line in my database query…
BhavikKama
  • 8,566
  • 12
  • 94
  • 164
0
votes
0 answers

Does CONVERT_TZ take daylight saving into account?

Possible Duplicate: MySQL CONVERT_TZ() Does CONVERT_TZ take daylight saving into account? For example: CONVERT_TZ('2013-1-1 12:00:00', 'US/Pacific', 'UTC'); I got the correct result, but I am not sure how to test DST scenario.
pixelfreak
  • 17,714
  • 12
  • 90
  • 109
0
votes
1 answer

Get unix timestamp from mysql database with offset applied

I have the offset in seconds to UTC, all timestamps stored on the database are in UTC, how to apply the offset during selection queries, mysql_query("SELECT * FROM table WHERE unix_timestamp=unix_timestamp with offset applied"); mysql_query("INSERT…
lbennet
  • 1,083
  • 5
  • 14
  • 31
0
votes
1 answer

Change timezone offset for YEARWEEK using COVERT_TZ function, php, mysql

Okay here is what I am trying to do: $timezone1 = '+00:00'; $timezone2 = '+08:00'; WHERE DATE(CONVERT_TZ(from_unixtime(comment_date), '$timezone1', '$timezone2')) = DATE_SUB(CURRENT_DATE,INTERVAL 1 DAY)"; That line gives me the total count of…
Georgi Grancharov
  • 183
  • 1
  • 2
  • 8
0
votes
2 answers

Would you ever use CONVERT_TZ() on a timestamp in a ORDER BY clause?

Can someone give me a reason why you would ever use CONVERT_TZ() on a timestamp in an ORDER BY clause. The timestamp, in this case, will always be in UTC. I found it in a piece of code I am using, which is buggy right now because of…
vinhboy
  • 8,542
  • 7
  • 34
  • 44
0
votes
1 answer

MySQL: Wondering how to select records based on day, after adjusting for timezone

I want to select records that are "currently available" based on specifics in the tables. I think it should be easy but, of course, I'm not seeing it. I'm hoping that asking here will jog my head enough that maybe I can answer my own question! I…
user781470
  • 1
1 2 3 4
5
6