Date question with mySQL/PHP

[Newsgroup_User]

I have a query that is pulling info from a mySQL database and is updating a
page on a daily basis. The info in the database is pre-loaded. I have set my
timezone on the server so if I login via putty and type in "date", I see the
correct date/time stamp for my timezone but the time is still GMT.

However, when my page queries the database, it's still grabbing info from
GMT. Obviously, I was mistaken that setting this timezone setting would
influence the webpage query. (and my query is going basically like:
SELECT *
FROM tableName
WHERE MONTH(fieldname) = MONTH(CURRENT_DATE) AND YEAR(fieldname) =
YEAR(CURRENT_DATE) AND fieldname <= CURRENT_DATE()

My thinking on this was to limit the items displayed to only those specified
for the current month. While this works, if there are better suggestions for
the query and suggestions on how to get "new date" items to show when it
reaches midnight for my local time and not GMT, I would really appreciate
it!

[Newsgroup_User]

Sorry, didn't setup the NNTP acct properly at first, this question was from
me.

- Hunter

[Newsgroup_User]

forums.macromedia.com wrote:

> I have a query that is pulling info from a mySQL database and is updating a
> page on a daily basis. The info in the database is pre-loaded. I have set my
> timezone on the server so if I login via putty and type in "date", I see the
> correct date/time stamp for my timezone but the time is still GMT.
>
> However, when my page queries the database, it's still grabbing info from
> GMT. Obviously, I was mistaken that setting this timezone setting would
> influence the webpage query. (and my query is going basically like:
> SELECT *
> FROM tableName
> WHERE MONTH(fieldname) = MONTH(CURRENT_DATE) AND YEAR(fieldname) =
> YEAR(CURRENT_DATE) AND fieldname <= CURRENT_DATE()
>
> My thinking on this was to limit the items displayed to only those specified
> for the current month. While this works, if there are better suggestions for
> the query and suggestions on how to get "new date" items to show when it
> reaches midnight for my local time and not GMT, I would really appreciate
> it!
>
>

... fieldname <= addtime(now(),'0:2:0') //2 hours east of GMT

subtime(now(),'0:5:0') //5 hours west of GMT

Mick

[Newsgroup_User]

"Mick White" <himselfBOGUS@mickweb.com> wrote in message >
> ... fieldname <= addtime(now(),'0:2:0') //2 hours east of GMT
>
> subtime(now(),'0:5:0') //5 hours west of GMT

Mick, thanks for this.. however, that didn't work for some reason - I
probably typed it in wrong. However it did get me to do some more hunting
around. I did find my solution after a lot of digging around on mysql.com.
Going to a command line on the server, at a mysql prompt, I did this:
SELECT @@global.time_zone, @@session.time_zone;
and that now has mySQL reading the timezone I have set on the server.

thanks again, Mick! it got me on the right track!

Hunter

[Newsgroup_User]

.oO(forums.macromedia.com)

>I have a query that is pulling info from a mySQL database and is updating a
>page on a daily basis. The info in the database is pre-loaded. I have set my
>timezone on the server so if I login via putty and type in "date", I see the
>correct date/time stamp for my timezone but the time is still GMT.

I'm not sure I understand: It shows the correct time for your time zone,
but a 'GMT' after it even if it should be something else?

>However, when my page queries the database, it's still grabbing info from
>GMT. Obviously, I was mistaken that setting this timezone setting would
>influence the webpage query.

It should. By default MySQL will take the TZ setting from the machine it
runs on, unless you configure it to something else.

>(and my query is going basically like:
>SELECT *
>FROM tableName
>WHERE MONTH(fieldname) = MONTH(CURRENT_DATE) AND YEAR(fieldname) =
>YEAR(CURRENT_DATE) AND fieldname <= CURRENT_DATE()

There's also a timezone setting in recent PHP versions, which should be
set if you work with PHP's date and time functions:

date_default_timezone_set()
http://www.php.net/date_default_timezone_set

Micha

[Newsgroup_User]

"Michael Fesser" <netizen@gmx.de> wrote in message
news:q4tbo3lbprq8t71k05t24tuebqkhijob5p@4ax.com...
> It should. By default MySQL will take the TZ setting from the machine it
> runs on, unless you configure it to something else.

it wasn't, unfortunately - but as this is a VPS2 install, it was probably
something I did not setup or did not setup correctly

> There's also a timezone setting in recent PHP versions, which should be
> set if you work with PHP's date and time functions:

I don't think it was so much a PHP issue as it was a mySQL issue, Micha. I
went to a mySQL command line and entered in:
SELECT @@global.time_zone, @@session.time_zone;
This appears to have solved my issue.