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

How to support datetime comparison in a query on a server that does not support timezone tables

I have this query: SELECT * FROM article WHERE online= 1 AND IF(ISNULL(dateHOnline), dateHCreation, dateHOnline) <= Convert_TZ(Now(), "SYSTEM", "Europe/Paris") But Convert_TZ(Now(),"SYSTEM","Europe/Paris") returns null, probably because my mysql…
Oliver
  • 23,072
  • 33
  • 138
  • 230
0
votes
1 answer

how to set for system's timezone with mysql timezone?

When I used for below function, SELECT timediff( now() , convert_tz( now() , @@session.time_zone ,'+00:00')) ; it shows -07:00:00 also the NOW() shows datetime different than my system's datetime. also with SHOW VARIABLES LIKE '%time_zone%' it…
Heena
  • 754
  • 5
  • 18
  • 30
0
votes
0 answers

my query is incorrect. convert_tz is causing incorrect values

I use convert_tz for many queries that I use daily. Yesterday, it began yielding incorrect values. I seperated these lines from my query, and ran them separately. SELECT CURDATE() - interval 1 day as yesterday, CURDATE() as today,…
SuspectT3
  • 3
  • 2
0
votes
0 answers

convert_tz is taking on GMT at the end of the date although I'm converting to East Coast Time

So here the the time in the database... 2023-01-02 21:00:00 Here is the select that I use to get the information out of the database... SELECT job_id,job_desc, CONVERT_TZ(job_date, 'GMT', 'US/Eastern') "job_date", cust_auto_id, cost, …
BostonMacOSX
  • 1,369
  • 2
  • 17
  • 38
0
votes
1 answer

Converting a UTC time string to Unix time

In my database, I'm storing time as a BIGINT UTC value. In my test data SQL dump I'd like to see the time in UTC for readability. However, the MySQL unix_timestamp function expects time in the local time zone, which I'm trying to avoid. I've looked…
Frederik
  • 14,156
  • 10
  • 45
  • 53
0
votes
0 answers

Mysql str_to_date with convert_tz returns NULL

The following MYSQL query returns a date as expected: select str_to_date('Thu, 13 May 2021 10:40:00 +0000', '%a, %d %b %Y %H:%i:%s +0000') the_date; +---------------------+ | the_date | +---------------------+ |…
TenG
  • 3,843
  • 2
  • 25
  • 42
0
votes
1 answer

why SELECT in (IDENTIC: [version MySQL + SESSION.sql_mode + system_time_zone]) return NULL in CentOs 7 but GOAL + WARNING on W10?

I know the warning is by DST, but why different results with the QUERY: SELECT CONVERT_TZ('2002-10-23T15:57:03Z', 'SYSTEM', 'America/Bogota') when I run the QUERY in CentOs 7 with: [VERSION()] => 5.7.33 [@@SESSION.sql_mode] =>…
Stackoverflow
  • 449
  • 5
  • 13
0
votes
0 answers

SQL Query: Unify Timezones

In my DB there is a date-time (slot_start) field. Unfortunately the entries are expressed in two different time zones. For example: 2021-02-08T08:00:00+01:00 and 2021-02-08T07:00:00+00:00 Now I'm trying to list all entries in Central European Time…
HermannMeyer
  • 1
0
votes
1 answer

Mysql Timezone with React.js/Node.js

I have one Mysql DB with tables which I have to manage the timezone inside the tables. global.time_zone and session.time_zone are UTC. I will give a short example of one table that I would like to set some conditions. I have 5 different "IP": for…
Satyr_99
  • 1
  • 2
0
votes
1 answer

MySQL MIN and сonvert_tz

I'm trying to select MIN date after converting datetime to users timezone with convert_tz same goes with COUNT: MIN(CONVERT_TZ(dateTime,'+00:00', '+03:00')) AS dateTime, COUNT(CONVERT_TZ(dateTime,'+00:00', '+03:00')) AS daygroup, and it doesn't…
Atis
  • 63
  • 1
  • 8
0
votes
1 answer

UTC Conversion to User Timezone with Daylight Savings Time

OK I got my mySql timezone package installed from http://dev.mysql.com/downloads/timezones.html I'm also storing all my times with UTC_TIMESTAMP(); And my little tidbit of code: $_SESSION['user_tz'] = 'America/New_York'; $last =…
laphiloctete
  • 476
  • 1
  • 6
  • 20
0
votes
1 answer

Changing mysql time

I have a table , in which there is a date column. This column is of type timestamp. Data in column is showing time in IST time zone . I want to convert it's data into another timezone. Do you have any suggestions for it. I am using Mysql database.
Durga Dutt
  • 4,093
  • 11
  • 33
  • 48
0
votes
2 answers

DATE_FORMAT(CONVERT_TZ) coming up empty in MySQL

Can find many instances of date_format OR convert_tz questions but none with both, so sorry if this has been asked and answered. Anyway... I'm coming up empty with the following query. "SELECT... MAX(DATE_FORMAT(CONVERT_TZ(p.posted_on, 'UTC',…
Mike Karr
  • 11
  • 2
0
votes
0 answers

Converting timezone(convert_tz) in mysql

How does convert_tz works in mysql,while going through some mysql site on web I encountered this function,when I tried replicating it, it is giving me the null value. why isn't this working?. Following is the script SELECT CONVERT_TZ('2004-01-01…
Deepesh
  • 820
  • 1
  • 14
  • 32
0
votes
0 answers

Calculate the right moment with timezones

i have a user table with timezone (example: America/Bogota) and the timezone offset (example: -25200). We have a webserver in germany (Europe/Berlin) and want to send emails on the right moment when the user in usa, africa or asia has 6am. I getting…
user6411365
  • 9
  • 1
1 2 3
4
5 6