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
129
votes
9 answers

convert_tz returns null

I know this sounds stupid, but when I use SELECT CONVERT_TZ('2004-01-01 12:00:00','UTC','Asia/Jakarta') AS time it outputs NULL. I'm using MySQL Workbench in Ubuntu 12.04 64 bit, and it works in my other laptop/os (also using MySQL Workbench).
mohur
  • 1,785
  • 4
  • 16
  • 25
90
votes
6 answers

Can MySQL convert a stored UTC time to local timezone?

Can MySQL convert a stored UTC time to local time-zoned time directly in a normal select statement? Let's say you have some data with a timestamp (UTC). CREATE TABLE `SomeDateTable` ( `id` int(11) NOT NULL auto_increment, `value` float NOT…
Johan
  • 20,067
  • 28
  • 92
  • 110
63
votes
3 answers

How to Convert UTC Date To Local time Zone in MySql Select Query

I am using this Where Condition in One Of my query with MySql Database.My Problem is that i have one displaytime column in my table but that table column shows the data in UTC Time.and i want to convert that displaytime column in the Local Time…
BhavikKama
  • 8,566
  • 12
  • 94
  • 164
47
votes
3 answers

MySQL CONVERT_TZ()

I am trying to set up a database that stores daily alert times as specified by users. For example, the user wants to receive an alert if some criterion is met each day between 7:00 AM and 7:30 AM. In trying to implement this, I need to accommodate…
johnnyspo
  • 471
  • 1
  • 4
  • 4
32
votes
9 answers

MySQL query for current GMT time

This sounds simple enough but I haven't been able to figure out how to use a simple SELECT statement to return the current time in GMT. I have been trying to use CONVERT_TZ() to convert NOW() to GMT based on the server time zone and the GMT time…
Russell C.
  • 1,649
  • 6
  • 33
  • 55
8
votes
1 answer

MySql search time stamp based on timezone offset

I have created a sample table with same scenario as my original one. Table name "records" in database "test" database timezone is set to UTC (SET time_zone = "+00:00";) `records` (`id`, `name`, `time_created`) (1, 'motion', '2017-09-13…
Harjeevan Tanda
  • 93
  • 8
8
votes
2 answers

MySQL CONVERT_TZ() returns null when one of the arguments is "+14:00"

I am trying to convert UTC time into the users local time, it works fine until I try to convert the time to +14:00 timezone, the result is always null, anyone has idea? Here is my code: select CONVERT_TZ(now(), '+00:00', '+14:00')
Lu Shi
  • 93
  • 1
  • 5
7
votes
3 answers

How to store datetimes in UTC and local timezone

What is a practical way to store datetimes so that I can let users view/query data as of their own local time while keeping information about the original datetime. Basically, users want to be able to query (as of their own local time) data…
Ronnis
  • 12,593
  • 2
  • 32
  • 52
7
votes
5 answers

TimeZone discrepancy in mysql and java

I have a query in mysql which compares 2 dates like this convert_tz(updatedDate,'+05:30','-05:00') < ? the convert function returns the value of column createddate in US Time. when I run this query in mysql query browser…
Bhavik Shah
  • 5,125
  • 3
  • 23
  • 40
6
votes
2 answers

Solr: org.apache.solr.common.SolrException: Invalid Date String:

I am new to solr and this is my first attempt at indexing solr data, I am getting the following exception while indexing, org.apache.solr.common.SolrException: Invalid Date String:'2011-01-07' at…
sesmic
  • 928
  • 4
  • 15
  • 32
6
votes
1 answer

The best way to convert time zone efficiently in MYSQL query

My table 'my_logs' have about 20,000,000 records, and I want to find out how many logs I have in each date within a few days. I want to have a result like +------------+---------+ | date | count | +------------+---------+ | 2016-07-01 | …
林鼎棋
  • 1,995
  • 2
  • 16
  • 25
4
votes
1 answer

Convert local TIME_STAMP to UTC time

I have two tables with time stamps TABLE1 with TIME_STAMP in local time TABLE2 with TIME_STAMP in UTC I need to do something like select count(*) from TABLE1 where TIME_STAMP > TABLE2.TIME_STAMP The problem is that this app will be deployed in…
storm_to
  • 1,495
  • 2
  • 16
  • 24
4
votes
1 answer

date time right in app but wrong in mysql [time zone]

Problem: Right time in app server, wrong in database. I am in China, Time Zone is UTC+8 I use hibernate. Entity definition as following (language: Scala) class CargoJournal { …
wyb
  • 91
  • 1
  • 5
4
votes
4 answers

Mysql timezone and selecting rows from one day

I use MySQL DATETIME column to store date & time. Dates are in UTC. I want to select item from one day. What i'm doing now: SELECT * FROM data WHERE DATE(CONVERT_TZ(datetime, 'UTC', 'Australia/Sydney')) = '2012-06-01' note that the timezone depends…
Martin Petercak
  • 833
  • 1
  • 9
  • 11
3
votes
2 answers

converting datetime to different timezone in C++/boost

I have price data for the Indian stock market, but the time and date stamp on it is GMT, so I can work with the data representing the correct date and time. I need to convert the date and time for some of the records in my DB into IST from GMT, my…
user1155299
  • 877
  • 5
  • 20
  • 29
1
2 3 4 5 6