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

SQL - compare dates with different time zones

I have a field in my MySQL database containing a date with a timezone that has the following format: 1986-07-10T00:00:00.000+02:00 or 1986-07-10T00:00:00.000Z (Z meaning zulu time, or UTC). I am trying to compose a WHERE clause that checks if the…
Peter
  • 33
  • 1
  • 3
2
votes
1 answer

mysql: convert_tz ignored in where clause comparisons?

So I have a healthy table of hourly stats where the day and time are split rather than kept as a singular datetime: +-------+-------------+------------+----------+ | cakes | pies | day | hour …
Alkanshel
  • 4,198
  • 1
  • 35
  • 54
2
votes
1 answer

using convert_tz for datetime comparisons

I have a table with day/time values that are split into date and an int fields respectively rather than a single datetime. Each row has a unique combo of date and hour, so there are 24 rows per each day. My challenge is selecting data relevant for…
Alkanshel
  • 4,198
  • 1
  • 35
  • 54
2
votes
1 answer

MySQL: automatic mapping of UTC to Local Timezone

I have a forum where users can post comments. When a comment is created its corresponding datetime value is stored in UTC format. I intend to present the data in local time, say 'ASIA/SINGAPORE'; 2 options: use convert_tz each time querying the…
olix20
  • 794
  • 1
  • 11
  • 23
2
votes
1 answer

comparing multiple dates within mySQL

We are working on a booking system that needs to support split appointments. These appointments have a first half, a break during which something else can be booked and then the second half. The system also supports normal bookings which have a…
direct00
  • 392
  • 4
  • 15
2
votes
1 answer

HQL with convert timezone

I have the following query in SQL Select count(*) as cnt, DATE_FORMAT(CONVERT_TZ(wrdTrk.createdOnGMTDate,'+00:00',:zone),'%Y-%m-%d') as dat from t_twitter_tracking wrdTrk where wrdTrk.word like (:word) and wrdTrk.createdOnGMTDate between …
sesmic
  • 928
  • 4
  • 15
  • 32
2
votes
4 answers

Detect if a date is in Daylight saving time in MySql

I have inherited a legacy application where all the dates and times are stored in the local timezone (UK). I am not in a position to change how these are stored. However, the requirement is to display all the dates in GMT within the app. Therefore…
Stephen Maher
  • 228
  • 5
  • 11
2
votes
3 answers

grails/mysql timezone change

Whats the best way to accomplish changing the timezone of an app? The way I see it the following must occur: Server TZ is changed by sys admin mysql must be restarted. every time based column in the database must have all values updated, using…
hvgotcodes
  • 118,147
  • 33
  • 203
  • 236
2
votes
2 answers

Ruby convert_tz using MariaDB is returning nil

When doing a regular: select convert_tz(now(), "UTC", "Europe/London") directly on a MariaDB server, it will produce: +-------------------------------------------+ | convert_tz(now(), "UTC", "Europe/London")…
renemadsen
  • 161
  • 1
  • 9
2
votes
0 answers

CONVERT_TZ functions on a shared hosted server

I this I would really like to use the CONVERT_TZ function to handle sending out automated reminders via cron jobs written in Perl. The problem I'm having is that anytime I use the CONVERT_TZ function I get a NULL output which leads me to believe…
Scott Nipp
  • 121
  • 12
2
votes
3 answers

Timezone issues using unix time, php, mysql

I spend over 8 hours last night to check all the documentations, browsing various post etc and I almost fixed my problem, yet there is stil something missing that I can't understand. I have a table which stores various user submissions with a unix…
Georgi Grancharov
  • 183
  • 1
  • 2
  • 8
1
vote
1 answer

How to get the last entry for each day using MySQL with CONVERT_TZ()

So I'm using this code to get the last entry for each day from a database. SELECT a.id, a.userid, a.jurisdiction, a.country, a.date FROM dk_location_records AS a, (SELECT userid, DATE(date) AS just_date, MAX(date) AS date FROM…
Jack Lenox
  • 357
  • 1
  • 5
  • 14
1
vote
3 answers

Convert timestamps stored in a Mongo collection

I have various timestamps stored in Mongo collections, some as floats and some as ints. They are all stored in BST and the server will be switched to UTC soon. How do I convert them within Mongo to be UTC timestamps? In MySQL I can do this: UPDATE…
bcmcfc
  • 25,966
  • 29
  • 109
  • 181
1
vote
1 answer

How can I make the query ask for all future dates instead of today?

I'm wanting to change the mySQL request to ask for all events in the database from today onward. Right now it is just asking for all events only on thdays date. This is what I have: $query = "SELECT * FROM events WHERE…
Joel
  • 2,691
  • 7
  • 40
  • 72
1
vote
1 answer

MySql - Finding "today" and "yesterday" while using CONVERT_TZ

I am struggling to get the correct definition of "today" and "yesterday" while using CONVERT_TZ() in MySql. My dates are stored in UTC, and I need to query the dates coming out in MST timezone, so something like this produces the correct start time…
slthomason
  • 373
  • 2
  • 4
  • 12
1
2
3 4 5 6