Using MySQL, php

http://www.freecrm.x10hosting.com

Before setting up my tables, I need to better understand the relevance of the
datetime and conversion of this field.

I am based in th UK (UTC, Greenwich mean time), but my host is in the states,
which means that the "now" date and time is based on US time (UTC - ?)

I have done formatting of the date i.e. <?php echo date('D,
d/m/Y',strtotime($datetime['DATETIME'])); ?> but am not sure how to approach
the problem relative to the timezone of any visitor in the world.

I have a sniffer script which detects the browsers language but I can't see
that being of much use.

The site does require a register/login so I suppose I could have a UTC
preference (i.e. UTC + 3 or whatever) set into their record. This could then
be used something like this.

<?php echo date('D,
d/m/Y',strtotime($datetime['DATETIME']+$row_recordsetuser['UTCTIMEZONEPREFERENCE
']));?>

Does this make sense?

I know the formatting is going to be a challenge as well because the US uses
m/d/Y,

Or is there a way of formatting the MySQL data before its even called?

.oO(RichardODreamweaver)

>Using MySQL, php
>
> http://www.freecrm.x10hosting.com
>
> Before setting up my tables, I need to better understand the relevance of the
>datetime and conversion of this field.
>
> I am based in th UK (UTC, Greenwich mean time), but my host is in the states,
>which means that the "now" date and time is based on US time (UTC - ?)

Check date_default_timezone_set().

> I have done formatting of the date i.e. <?php echo date('D,
>d/m/Y',strtotime($datetime['DATETIME'])); ?> but am not sure how to approach
>the problem relative to the timezone of any visitor in the world.

You can only deal with your server's timezone setting.

> I have a sniffer script which detects the browsers language but I can't see
>that being of much use.

It's of absolutely no use. English and Spanish for example are spoken
all around the world, probably somewhere in almost every timezone.

> The site does require a register/login so I suppose I could have a UTC
>preference (i.e. UTC + 3 or whatever) set into their record.

Possible. If UTC/GMT is not enough, you could let the users choose the
offset themselves. This is how it's done in many popular forum scripts.

>This could then
>be used something like this.
>
> <?php echo date('D,
>d/m/Y',strtotime($datetime['DATETIME']+$row_recordsetuser['UTCTIMEZONEPREFERENCE
>']));?>
>
> Does this make sense?
>
> I know the formatting is going to be a challenge as well because the US uses
>m/d/Y,

YYYY-MM-DD

Works always. ;-)

> Or is there a way of formatting the MySQL data before its even called?

Of course you can also let MySQL return an already formatted date, which
is usually the preferred and most efficient way. Have a look at MySQL's
date and time functions, especially DATE_FORMAT().

Micha

Thanks Micha - sorry for the late reply - I had some reading to do!

Apparently, the server datetime cannot itself be altered and the formatting
can only be changed during the MySQL call functions.

UTC_TIMESTAMP() recalls the server date and time in UTC format, which can then
be manipulated using the saved login preferences as you suggested.

I take it that this is more efficient than using php to convert after the data
has been called in a recordset.

I must admit, using YYYY-MM-DD H:i:s would be much simpler and easier to
manage but I'm unsure how many users accept this format - in the UK, it causes
many problems (even though it is the most logical string)

One thing that puzzles me though... I have used the phpBB3 forum functionality
(Brilliant!!) and, after checking the format of the dates and times, they are
all in VARCHAR with what looks like seconds - is this another system I'm not
aware of? I think UNIX_TIMESTAMP() works like this?

Rich

OK, my server doesn't support UTC_TIMESTAMP!

Back to plan Z!

As an international format, would this be OK?

D, d M Y H:i:s

Wed, 19 Jan 2008 20:15:34

OK - think I've sussed it.

All date fields will be in VARCHAR, using the Time() function to store as
UTC/GMT seconds.

The user can choose a preference for Timezone (e.g. GB, TCU, Etc/GMT+1)

Also, the user can select a time format which is strored.

On login, these preferences are stored as Session variables and used on each
page to set the timezone.

putenv ("TZ=".$_SESSION['timezone'])

All retrieved data can be expressed using the format preference ($utf):

echo date($utf, ($now))

Job done!

.oO(RichardODreamweaver)

>OK - think I've sussed it.
>
> All date fields will be in VARCHAR, using the Time() function to store as
>UTC/GMT seconds.
>
> The user can choose a preference for Timezone (e.g. GB, TCU, Etc/GMT+1)
>
> Also, the user can select a time format which is strored.
>
> On login, these preferences are stored as Session variables and used on each
>page to set the timezone.
>
> putenv ("TZ=".$_SESSION['timezone'])

You should use date_default_timezone_set() instead:

| Note: Since PHP 5.1.0 (when the date/time functions were rewritten),
| every call to a date/time function will generate a E_NOTICE if the
| timezone isn't valid, and/or a E_STRICT message if using the system
| settings or the TZ environment variable.

http://www.php.net/manual/en/functio...mezone-set.php

Micha

On Fri, 18 Jul 2008 08:24:26 +0200, Michael Fesser <netizen@gmx.de>
wrote:

>You should use date_default_timezone_set() instead:

True if your PHP version is => 5.1. My approach is usually something
along the lines of:

function setTimeZone($z){
if(function_exists('date_default_timezone_set'))
date_default_timezone_set($z);
else
putenv("TZ=$z");
}

Gary

[q]Originally posted by: Newsgroup User

You should use date_default_timezone_set() instead:
[/q]

Thanks Micha

Gary - as my server does support it, I'm not sure why I need the if function
exists code?

On Fri, 18 Jul 2008 22:04:55 +0000 (UTC), "RichardODreamweaver"
<webforumsuser@macromedia.com> wrote:

> Gary - as my server does support it, I'm not sure why I need the if function
>exists code?

It's just a safeguard. If the date_default_timezone_set() function is
supported, it is executed. On the other hand, if you later change
hosts and wind up with a server that does not support it, it won't
generate an error and fail. It will simply use the putenv() function
instead.

Gary

.oO(Gary White)

>On Fri, 18 Jul 2008 22:04:55 +0000 (UTC), "RichardODreamweaver"
><webforumsuser@macromedia.com> wrote:
>
>> Gary - as my server does support it, I'm not sure why I need the if function
>>exists code?
>
>It's just a safeguard. If the date_default_timezone_set() function is
>supported, it is executed. On the other hand, if you later change
>hosts and wind up with a server that does not support it, it won't
>generate an error and fail.

I don't think that this is very likely. Every good host should at least
support 5.1. Changing to another host should not become a "downgrade".

Anyway, another possible solution might be something like this:

if (!function_exists('date_default_timezone_set')) {
function date_default_timezone_set($z) {
putenv("TZ=$z");
}
}

I use a similar thing in my own scripts for the function str_getcsv().
It is already in the CVS, but not yet available in the stable releases.
But since I already want to use it, I simply use my own implementation
in a code block like above until the official version becomes available.

Micha