Select from mysql by day with different timezones (php)

Posted by Adam on Stack Overflow See other posts from Stack Overflow or by Adam
Published on 2010-03-20T10:35:33Z Indexed on 2010/03/20 10:41 UTC
Read the original article Hit count: 386

Filed under:

mysql

|

timezone

|

php

|

select

I'm storing leads in a database, and each lead has a datetime field with a PST timezone based date & time.

I want my user to be able to display all leads from a certain date (e.g. today, yseterday), and choose the timezone.

E.g. if I want to see all leads that were generated yesterday in EST timezone, I need to first convert (or read) all the datetime values to EST, and then only select those who are in the right range (yesterday).

What would be the best way to do that?

© Stack Overflow or respective owner

Related posts about mysql

How to remove MySQL completely with config and library files on ubuntu 12.04 gnome 3.0

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
I tried everything till now: sudo apt-get remove mysql-server mysql-client mysql-common sudo apt-get purge mysql-server mysql-client mysql-common sudo apt-get autoremove and even more commands... But whenever I am trying to locate mysql. I get a no. of files related to mysql command: shell>>… >>> More
mysql: Cannot load from mysql.proc. The table is probably corrupted

as seen on Server Fault - Search for 'Server Fault'
Mysql was started: /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 but when I'm trying to do something: ERROR 1548 (HY000) at line 1: Cannot… >>> More
Why is there a /etc/init.d/mysql file on this Slackware machine? How could it have gotten there?

as seen on Server Fault - Search for 'Server Fault'
A client of my IT-consulting service owns a web-development shop. He's been having problems with a Slackware 12.0 server running MySQL 5.0.67. The machine was set up by the client's sysadmin, who left on bad terms. My client no longer employs a sysadmin. As far as I can tell, the only copy of MySQL… >>> More
mysql: Bind on unix socket: Permission denied

as seen on Server Fault - Search for 'Server Fault'
Can't start mysql with: sudo /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 120222 13:40:48 mysqld_safe Starting mysqld daemon with databases… >>> More
MySQL – Learning MySQL Online in 6 Hours – MySQL Fundamentals in 320 Minutes

as seen on SQL Authority - Search for 'SQL Authority'
MySQL is one of the most popular database language and I have been recently working with it a lot. Data have no barrier and every database have their own place. I have been working with MySQL for quite a while and just like SQL Server, I often find lots of people asking me if I have a tutorial which… >>> More

Related posts about timezone

PostgreSQL timezone does not match system timezone

as seen on Server Fault - Search for 'Server Fault'
I have several PostgreSQL 9.2 installations where the timezone used by PostgreSQL is GMT, despite the entire system being "Europe/Vienna". I double-checked that postgresql.conf does not contain timezone setting, so according to the documentation it should fallback to the system's timezone. However… >>> More
.NET Get timezone offset by timezone name

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello, In database I store all date/times in UTC. I know user's timezone name ("US Eastern Standard Time" for example). In order to display correct time I was thinking that I need to add user's timezone offset to UTC date/time. But how would I get timezone offset by timezone name? Thank You! >>> More
Building TimeZone object in Java

as seen on Stack Overflow - Search for 'Stack Overflow'
I build a Java TimeZone object using the TimeZone String like GMT-8,GMT,PST,EST etc. This did not consider whether the timezone is daylight saving enabled or not.Now there is a requirement to include this check and instead of giving the input as PDT or EDT the daylight saving is given as a seperate… >>> More
Running Webapp on Mac in UTC (either changing MacBook timezone or tomcat timezone)

as seen on Super User - Search for 'Super User'
To run my web app, I need to set my timezone to UTC on my MacBook. I can do this temporarily by opening a Konsole and entering sudo ln -sf /usr/share/zoneinfo/UTC /etc/localtime However, my timezone returns to normal when I restart my machine! Any advice? Edit : The response to this question… >>> More
Setting session timezone with spring jdbc oracle

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello I have a spring/jdbc/oracle 10g application. The Oracle server database timezone is set to GMT + 2 JVM timezone is GMT + 2 (even though it doesn't matter in my case). I have a stored procedure that performs some date operations. The problem is that session timezone is different(GMT) than database… >>> More