422,023 Members | 1,005 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,023 IT Pros & Developers. It's quick & easy.

DATE formatting and TIME zones - how to deal with this hell?

P: n/a
There is a function: http://paste.ubuntu.com/21865

It needs GMT date in YYYY-MM-DD HH:MM:SS format - in SQL: [b]datetime[/
b]. If date is the same as today, the function returns "Today". There
is one problem. This function does not recognize time zones.

How to adjust date to user's time zone? Is converting to timestamp()
and then to format readable to visitors the one and only solution
(e.g. strtotime() + date() OR DateTime object)? Perhaps, it's possible
to solve this problem with mathematical operations (it needs more
combinations). Has someone dealed with time zones?

If I stored timestamps instead of datetime, PHP would deal with time
zones automatically (date() with 2nd parameter). However, it will be
harder to display "Today", "Tomorrow", "Yesterday"...

In conclusion, this function must return formatted string in proper
time zone. It must return "Today" and optionally "2 minutes ago" when
needed. How to do it? Is it better to make mathematical operations? Or
should I use build-in functions like: mktime(), strftime(), date(),
strtotime()...? Everything must be very FAST.
Jun 27 '08 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Greetings, WebCM.
In reply to Your message dated Sunday, June 22, 2008, 01:57:51,
There is a function: http://paste.ubuntu.com/21865
It needs GMT date in YYYY-MM-DD HH:MM:SS format - in SQL: [b]datetime[/
b]. If date is the same as today, the function returns "Today". There
is one problem. This function does not recognize time zones.
How to adjust date to user's time zone? Is converting to timestamp()
and then to format readable to visitors the one and only solution
(e.g. strtotime() + date() OR DateTime object)? Perhaps, it's possible
to solve this problem with mathematical operations (it needs more
combinations). Has someone dealed with time zones?
If I stored timestamps instead of datetime, PHP would deal with time
zones automatically (date() with 2nd parameter). However, it will be
harder to display "Today", "Tomorrow", "Yesterday"...
In conclusion, this function must return formatted string in proper
time zone. It must return "Today" and optionally "2 minutes ago" when
needed. How to do it? Is it better to make mathematical operations? Or
should I use build-in functions like: mktime(), strftime(), date(),
strtotime()...? Everything must be very FAST.
So, please, correct me if I wrong...
You want to show textual representation of 3 nearby days, in user timezone?
It's really simple.
First, you must store meaningful info in database.
If you storing date - store it as date.
Second, retrieve data from database in a format you want to use in script.
You want UNIX timestamp? Convert date to it in SQL query.
Don't ask me "How?", read the f***ing manual. It's well explanatory.

To your solution.
1. Read PHP documentation, realize that you can set your script to work in
specified timezone. Key word is date_default_timezone_set
2. To detect, if we are "today/tomorrow/yesterday", just use simple match.
And remember, that there is NO way to have post entered "tomorrow", unless you
are hand-crafted it in database. But still...

<?php

// $post_time - subjected datetime
date_default_timezone_set(user timezone);
$today = (int)((time() + date('Z')) / 86400);
$post_day = (int)(($post_time + date('Z')) / 86400) - $today;

// Weird code begin here
if($post_day == -1)
{
$post_day = configLangGet('Yesterday');
}
else if($post_day == 1)
{
$post_day = configLangGet('Tomorrow');
}
else if($post_day == 0)
{
$post_day = configLangGet('Today');
}
else
{
$post_day = strftime(format, $post_time);
}
?>
--
Sincerely Yours, AnrDaemon <an*******@freemail.ru>

Jun 27 '08 #2

P: n/a
AnrDaemon wrote:
Greetings, WebCM.
In reply to Your message dated Sunday, June 22, 2008, 01:57:51,
>There is a function: http://paste.ubuntu.com/21865
>It needs GMT date in YYYY-MM-DD HH:MM:SS format - in SQL: [b]datetime[/
b]. If date is the same as today, the function returns "Today". There
is one problem. This function does not recognize time zones.
>How to adjust date to user's time zone? Is converting to timestamp()
and then to format readable to visitors the one and only solution
(e.g. strtotime() + date() OR DateTime object)? Perhaps, it's possible
to solve this problem with mathematical operations (it needs more
combinations). Has someone dealed with time zones?
>If I stored timestamps instead of datetime, PHP would deal with time
zones automatically (date() with 2nd parameter). However, it will be
harder to display "Today", "Tomorrow", "Yesterday"...
>In conclusion, this function must return formatted string in proper
time zone. It must return "Today" and optionally "2 minutes ago" when
needed. How to do it? Is it better to make mathematical operations? Or
should I use build-in functions like: mktime(), strftime(), date(),
strtotime()...? Everything must be very FAST.

So, please, correct me if I wrong...
You're wrong. As usual.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Jun 27 '08 #3

P: n/a
WebCM wrote:
There is a function: http://paste.ubuntu.com/21865

It needs GMT date in YYYY-MM-DD HH:MM:SS format - in SQL: [b]datetime[/
b]. If date is the same as today, the function returns "Today". There
is one problem. This function does not recognize time zones.

How to adjust date to user's time zone? Is converting to timestamp()
and then to format readable to visitors the one and only solution
(e.g. strtotime() + date() OR DateTime object)? Perhaps, it's possible
to solve this problem with mathematical operations (it needs more
combinations). Has someone dealed with time zones?

If I stored timestamps instead of datetime, PHP would deal with time
zones automatically (date() with 2nd parameter). However, it will be
harder to display "Today", "Tomorrow", "Yesterday"...

In conclusion, this function must return formatted string in proper
time zone. It must return "Today" and optionally "2 minutes ago" when
needed. How to do it? Is it better to make mathematical operations? Or
should I use build-in functions like: mktime(), strftime(), date(),
strtotime()...? Everything must be very FAST.
First of all, you don't necessarily know the client's timezone. All you
know for sure is the timezone on the server. There is nothing in either
PHP or MySQL to get that. You will need to use a client-side language
such as JavaScript or a Java applet to get the client time.

To use such a function, you need have access to the date/time stored in
the database. Storing as a datetime or timestamp has it's advantages -
you can retrieve it in a number of formats, such as UNIX_TIMESTAMP or
any number of other formats. It also makes it easy to sort on this
field. For instance, to get the format you need would be quite easy
with MySQL's DATEFORMAT function, i.e.

DATEFORMAT(datecol, '%Y-%m-%d %H:%i:%s');

Or, if the column is in unixtime, a simple FROM_UNIXTIME() call works.

The point is - there are a lot of options here - all of which work.
Don't restrict yourself to PHP - look at the database functions, also.

As for fast - this is not the time to be worried about speed - that's
over-optimizing. Rather, if it's a problem, later you can look at your
code and determine how to speed it up. Unless you write some awfully
inefficient code, this conversion will NOT be your problem.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Jun 27 '08 #4

P: n/a
Greetings, Jerry Stuckle.
In reply to Your message dated Sunday, June 22, 2008, 06:19:16,
WebCM wrote:
>There is a function: http://paste.ubuntu.com/21865

It needs GMT date in YYYY-MM-DD HH:MM:SS format - in SQL: [b]datetime[/
b]. If date is the same as today, the function returns "Today". There
is one problem. This function does not recognize time zones.

How to adjust date to user's time zone? Is converting to timestamp()
and then to format readable to visitors the one and only solution
(e.g. strtotime() + date() OR DateTime object)? Perhaps, it's possible
to solve this problem with mathematical operations (it needs more
combinations). Has someone dealed with time zones?

If I stored timestamps instead of datetime, PHP would deal with time
zones automatically (date() with 2nd parameter). However, it will be
harder to display "Today", "Tomorrow", "Yesterday"...

In conclusion, this function must return formatted string in proper
time zone. It must return "Today" and optionally "2 minutes ago" when
needed. How to do it? Is it better to make mathematical operations? Or
should I use build-in functions like: mktime(), strftime(), date(),
strtotime()...? Everything must be very FAST.
First of all, you don't necessarily know the client's timezone.
Forums typically has such configuration option for registered users.
How can you missed this simple point?
To use such a function, you need have access to the date/time stored in
the database. Storing as a datetime or timestamp has it's advantages -
you can retrieve it in a number of formats, such as UNIX_TIMESTAMP or
any number of other formats. It also makes it easy to sort on this
field. For instance, to get the format you need would be quite easy
with MySQL's DATEFORMAT function, i.e.
DATEFORMAT(datecol, '%Y-%m-%d %H:%i:%s');
Or, if the column is in unixtime, a simple FROM_UNIXTIME() call works.
Will it return "today" or "yesterday" as OP requested?
The point is - there are a lot of options here - all of which work.
Don't restrict yourself to PHP - look at the database functions, also.
As for fast - this is not the time to be worried about speed - that's
over-optimizing. Rather, if it's a problem, later you can look at your
code and determine how to speed it up. Unless you write some awfully
inefficient code, this conversion will NOT be your problem.

--
Sincerely Yours, AnrDaemon <an*******@freemail.ru>

Jun 27 '08 #5

P: n/a
AnrDaemon wrote:
Greetings, Jerry Stuckle.
In reply to Your message dated Sunday, June 22, 2008, 06:19:16,
>WebCM wrote:
>>There is a function: http://paste.ubuntu.com/21865

It needs GMT date in YYYY-MM-DD HH:MM:SS format - in SQL: [b]datetime[/
b]. If date is the same as today, the function returns "Today". There
is one problem. This function does not recognize time zones.

How to adjust date to user's time zone? Is converting to timestamp()
and then to format readable to visitors the one and only solution
(e.g. strtotime() + date() OR DateTime object)? Perhaps, it's possible
to solve this problem with mathematical operations (it needs more
combinations). Has someone dealed with time zones?

If I stored timestamps instead of datetime, PHP would deal with time
zones automatically (date() with 2nd parameter). However, it will be
harder to display "Today", "Tomorrow", "Yesterday"...

In conclusion, this function must return formatted string in proper
time zone. It must return "Today" and optionally "2 minutes ago" when
needed. How to do it? Is it better to make mathematical operations? Or
should I use build-in functions like: mktime(), strftime(), date(),
strtotime()...? Everything must be very FAST.
>First of all, you don't necessarily know the client's timezone.

Forums typically has such configuration option for registered users.
How can you missed this simple point?
Which has absolutely nothing to do with the ops question - or the
problem at hand. How can you miss this simple point? Oh, yes. I
forgot who you are. Now I know how you could miss something so simple.
>To use such a function, you need have access to the date/time stored in
the database. Storing as a datetime or timestamp has it's advantages -
you can retrieve it in a number of formats, such as UNIX_TIMESTAMP or
any number of other formats. It also makes it easy to sort on this
field. For instance, to get the format you need would be quite easy
with MySQL's DATEFORMAT function, i.e.
> DATEFORMAT(datecol, '%Y-%m-%d %H:%i:%s');
>Or, if the column is in unixtime, a simple FROM_UNIXTIME() call works.

Will it return "today" or "yesterday" as OP requested?
That is not what the op requested. Learn to read.
>The point is - there are a lot of options here - all of which work.
Don't restrict yourself to PHP - look at the database functions, also.
>As for fast - this is not the time to be worried about speed - that's
over-optimizing. Rather, if it's a problem, later you can look at your
code and determine how to speed it up. Unless you write some awfully
inefficient code, this conversion will NOT be your problem.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Jun 27 '08 #6

P: n/a
Next problem. There is no gmtime() function in PHP - what a fault!
Will PHP 6 introduce it? Current GMT timestamp may be achieved by:
time() - date('Z') but maybe there are easier ways? Other method:
date_default_timezone_set('GMT'). My CMS is multilingual. However, is
it worth to store all times in GMT? Or better in local timezone?

I must support MySQL -AND- SQLite. Maybe I will add another SQL engine
later. Okay, my questions are:

1. Is it needed to convert date to timestamp?
2. Should I generate date in SQL or in PHP?
3. How to format date for the best performance? As INT or DATETIME?
How about 2038?

Effects that I must achieve:
1. "5 minutes ago", "in 10 minutes"
2. Date and time in timezone set by user
3. "23.05.2008, 12:23" - format set by admin
4. "Today, 12:23"

I must support SQLite AND MySQL - so to format date in SQL, i would
have to create functions for this purpose during connections or on
demand - seperately for these engines.
Jun 27 '08 #7

P: n/a
WebCM wrote:
Next problem. There is no gmtime() function in PHP - what a fault!
Will PHP 6 introduce it? Current GMT timestamp may be achieved by:
time() - date('Z') but maybe there are easier ways? Other method:
date_default_timezone_set('GMT'). My CMS is multilingual. However, is
it worth to store all times in GMT? Or better in local timezone?

I must support MySQL -AND- SQLite. Maybe I will add another SQL engine
later. Okay, my questions are:

1. Is it needed to convert date to timestamp?
2. Should I generate date in SQL or in PHP?
3. How to format date for the best performance? As INT or DATETIME?
How about 2038?

Effects that I must achieve:
1. "5 minutes ago", "in 10 minutes"
2. Date and time in timezone set by user
3. "23.05.2008, 12:23" - format set by admin
4. "Today, 12:23"

I must support SQLite AND MySQL - so to format date in SQL, i would
have to create functions for this purpose during connections or on
demand - seperately for these engines.
I don't know about sqllite - I don't use it for anything but very
trivial stuff. I use MySQL for everything else - and all of this can be
done in MySQL.

As to storing - if you need a date, use a date type. But if you need a
date and a time, use a date/time value, such as a timestamp. And you
can do most everything you want in MySQL. See comp.databases.mysql for
more information.

As for your CMS supporting both - personally, I wouldn't worry about it.
Look at the other CMS's - most use MySQL; a few use PostGresSQL. None
use sqllite - it's really not suitable for heavy duty database
applications (hence the term sql *lite*).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Jun 27 '08 #8

P: n/a
On 25 Jun, 10:24, WebCM <webcm...@gmail.comwrote:
Next problem. There is no gmtime() function in PHP - what a fault!
Nearly correct. What you should have said is:
"Next problem. I am totally unable to read what is written in the
manual - what a fault!

I quote: "Identical to the date() function except that the time
returned is Greenwich Mean Time (GMT)."
Jun 27 '08 #9

P: n/a
Can you DIFFERENTIATE gmtime() and gmdate()? I've written clearly -
there is no >gmtime() << function. I'd like to get current UNIX
TIMESTAMP. Perhaps, it's possible by gmdate('U').

How about timezones? Should I store times in GMT in database or better
in local timezone for selected language (e.g. EN, DE, PL, EN/US)? My
CMS is multilingual. How should I achieve it?
date_default_timezone_set('GMT')? setlocale('en_US')?
Jun 27 '08 #10

P: n/a
Greetings, WebCM.
In reply to Your message dated Wednesday, June 25, 2008, 19:49:06,
Can you DIFFERENTIATE gmtime() and gmdate()? I've written clearly -
there is no >>gmtime() << function. I'd like to get current UNIX
TIMESTAMP. Perhaps, it's possible by gmdate('U').
Obviously,

gmdate('U') === date('U') === (string)time()

UNIXTIME has nothing to do with timezones.
It is what it is - amount of seconds passed from ....
RTFM.
How about timezones? Should I store times in GMT in database or better
in local timezone for selected language (e.g. EN, DE, PL, EN/US)? My
CMS is multilingual. How should I achieve it?
date_default_timezone_set('GMT')? setlocale('en_US')?
It's up to you. But I prefer to store dates as dates. Obviously, in my own
timezone, because database stored on my server.
That way, I will never run in time conflict, where reply beeing posted before
question.
How to deal with dates at display time - read my first answer in this thread.
--
Sincerely Yours, AnrDaemon <an*******@freemail.ru>

Jun 27 '08 #11

This discussion thread is closed

Replies have been disabled for this discussion.