473,325 Members | 2,608 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Using PHP with MySQL Datetime

I'm using PHP with MySQL 4.x and was having trouble converting a
datetime from MySQL into a formatted string until I ran across this
solution that converts a YYYY-MM-DD HH:MM:SS string into a Unix-style
timestamp and then formats it.

$timestamp = "2005-04-06 15:43:34";
$time = strtotime($timestamp);
print date('Y-m-d \a\t H:i', $time)."\n";

However, it seems kind of counter productive. After all, aren't people
(and RDBMs like MySQL) getting away from Unix timestamps for the a
reason? I don't necessarily think that my code will last until the
2038 timestamp rollover but I would rather avoid timestamps if possible
and am surprised that PHP doesn't seem to offer a better solution -
like parsing the string into some sort of date object or array, and
then formatting THAT into the desired string. Is there such a thing
right now?

It looks like date_parse() is a step in the right direction
(http://php.net/manual/en/function.date-parse.php) but it looks like it
is not available in a snapshot release and that's only half of the
equation anyway. I could always write my own function to do what I am
after but I would like to use something built in if possible. Any
ideas?

-Brandon

Jan 16 '07 #1
6 38870
>I'm using PHP with MySQL 4.x and was having trouble converting a
>datetime from MySQL into a formatted string until I ran across this
solution that converts a YYYY-MM-DD HH:MM:SS string into a Unix-style
timestamp and then formats it.
Have you considered letting MySQL format the timestamp the way you
want it? See date_format().
>$timestamp = "2005-04-06 15:43:34";
$time = strtotime($timestamp);
print date('Y-m-d \a\t H:i', $time)."\n";

However, it seems kind of counter productive. After all, aren't people
(and RDBMs like MySQL) getting away from Unix timestamps for the a
reason?
MySQL's underlying storage of dates doesn't use UNIX timestamps,
making it practical to use dates for a fairly wide range (although
it does have a Y10K problem and a Y0 problem) of historical dates,
so many genealogists can use it without any grief (not many can
trace their roots back to around the time of Jesus Christ). It can
convert to and from UNIX timestamps. I wish it had a way to do
things like add a number of seconds to a datetime giving another
datetime, and to subtract two datetimes giving a number of seconds.

PHP's underlying storage of dates, last time I looked, *IS* the UNIX
timestamp, with all the time range limits that involves.
>I don't necessarily think that my code will last until the
2038 timestamp rollover but I would rather avoid timestamps if possible
and am surprised that PHP doesn't seem to offer a better solution -
like parsing the string into some sort of date object or array,
C's "struct tm" might be an appropriate type of thing to use (fields
are broken out into year, month, day, hour, minute, and second)
although there's some awkwardness about that 1900-year offset on
tm_year. The only general way to do math on those I've seen used is
turning it into a UNIX timestamp (and possibly back again).
>and
then formatting THAT into the desired string. Is there such a thing
right now?
>It looks like date_parse() is a step in the right direction
(http://php.net/manual/en/function.date-parse.php) but it looks like it
is not available in a snapshot release and that's only half of the
equation anyway. I could always write my own function to do what I am
after but I would like to use something built in if possible. Any
ideas?
Jan 16 '07 #2
..oO(Gordon Burditt)
>It can
convert to and from UNIX timestamps. I wish it had a way to do
things like add a number of seconds to a datetime giving another
datetime, and to subtract two datetimes giving a number of seconds.
You should be able to do that with DATE_ADD() and TIME_TO_SEC().

Micha
Jan 16 '07 #3
$timestamp = "2005-04-06 15:43:34";
$time = strtotime($timestamp);
print date('Y-m-d \a\t H:i', $time)."\n";

However, it seems kind of counter productive. After all, aren't people
(and RDBMs like MySQL) getting away from Unix timestamps for the a
reason?

PHP's underlying storage of dates, last time I looked, *IS* the UNIX
timestamp, with all the time range limits that involves.
There's an "underlying" storage for dates? I wasn't aware that PHP had
a true date type for a timestamp for "lie under." Timestamps are just
integers. PHP may have a lot of functions for parsing strings into
integer timestamps and building strings from those integers but that
doesn't mean there's any trick to using representing a date with
another data type. I was just hoping that PHP would have something
built in for storing dates in a format other than timestamps. From the
look of the parse_date() function I mentioned, it looks like something
might be in the works but hasn't made its way to an official release
yet.

Actually, the PEAR libraries have a true date data type but I'm not
sure where my app is ultimately going to be deployed so I'd rather not
rely on it.

Jan 20 '07 #4
>There's an "underlying" storage for dates? I wasn't aware that PHP had
>a true date type for a timestamp for "lie under."
It doesn't need a *TRUE* date type, it just needs some way to store
dates (and for the date library functions to use as input and
output). And a 32-bit integer is pretty lame nowadays. And that
seems to be what PHP uses for the date library functions (on 32-bit
machines, anyway).
>Timestamps are just
integers.
This is not true in general, unless you're saying that "all bits
is just bits" and trinary is impossible. For example, in MySQL, a
timestamp has pieces year, month, day, hour, minute, and second.
In MS-DOS, a file timestamp also had these fields, but the number
of seconds was missing a bit so only even seconds were possible,
and the range of years were very limited. And some programs just
store dates as strings, with or without a time zone.

Oh, yes, it does matter whether a timestamp is implicitly in local
time, UTC, or something else. POSIX timestamps really have no good
way to store "the date of a transaction", meaning the year/month/day
local time a particular transaction happened. That date doesn't
change when the user moves from one timezone to another, and it can
have legal implications more significant than the UTC date/time.
>PHP may have a lot of functions for parsing strings into
integer timestamps and building strings from those integers but that
doesn't mean there's any trick to using representing a date with
another data type.
There is if you have to re-create all the functions for dealing
with time because, say, they have insufficient range.

>I was just hoping that PHP would have something
built in for storing dates in a format other than timestamps.
I consider "something built in for storing dates in a format" to
*BE* a timestamp by definition, even if it's not a POSIX timestamp.
>From the
look of the parse_date() function I mentioned, it looks like something
might be in the works but hasn't made its way to an official release
yet.
>Actually, the PEAR libraries have a true date data type but I'm not
sure where my app is ultimately going to be deployed so I'd rather not
rely on it.

Jan 20 '07 #5
There's an "underlying" storage for dates? I wasn't aware that PHP had
a true date type for a timestamp for "lie under."

It doesn't need a *TRUE* date type, it just needs some way to store
dates (and for the date library functions to use as input and
output). And a 32-bit integer is pretty lame nowadays. And that
seems to be what PHP uses for the date library functions (on 32-bit
machines, anyway).
All I wanted to know was whether I was missing a set of functions that
deal with a true date type (something that stores the parts of a date
separately and takes timezones into account) or if integer timestamps
were all that PHP provides functions for right now.

By the way, integer timestamps are definitely not 32-bits. If they
were, timestamps would have overflowed in less than two months. Any
place I've seen them in a typed language (C/C++, Java, etc.), they have
been 64-bit "long" integers.
Timestamps are just
integers.

This is not true in general, unless you're saying that "all bits
is just bits" and trinary is impossible. For example, in MySQL, a
timestamp has pieces year, month, day, hour, minute, and second.
In MS-DOS, a file timestamp also had these fields, but the number
of seconds was missing a bit so only even seconds were possible,
and the range of years were very limited. And some programs just
store dates as strings, with or without a time zone.
It sounds like you might be using the word "timestamp" in a much more
general way but a true timestamp is, by definition, just a long integer
representing the time since Jan 1, 1970 Greenwich Mean Time. What you
are describing are other representations of a date/time, which is
exactly what I am after.

In fact, since you mentioned MySQL, the "timestamp" type in MySQL 4.1+
is ACTUALLY a "datetime" object, which is a more complex representation
of a date and time including a timezone. Prior to version 4.1, MySQL's
timestamp type was actually a true POSIX timestamp - all you got back
from a query was a number.
I was just hoping that PHP would have something
built in for storing dates in a format other than timestamps.

I consider "something built in for storing dates in a format" to
*BE* a timestamp by definition, even if it's not a POSIX timestamp.
So are you saying that there IS an alternative in PHP? Because so far,
I haven't found one. Just look at the documentation at PHP.net and
look at the example I gave.

$timeStr = "2005-04-06 15:43:34";
$time = strtotime($timeStr);
print date('Y-m-d \a\t H:i', $time)."\n";

$timeStr is just a plain old string and strtotime() parses it into a
POSIX timestamp, which is then passed around to wherever it needs to go
and most of PHP's functions take UNIX timestamps.

Jan 22 '07 #6
..oO(Brandon)
>By the way, integer timestamps are definitely not 32-bits.
The usual Unix timestamp is still a 32-bit signed integer.
>If they
were, timestamps would have overflowed in less than two months.
Such an "integer timestamp" counts seconds, not milli seconds or
whatever. With a signed 32-bit integer you can count up to at least
2^31 seconds, which is enough for more than 68 years. With negative
timestamps the range is doubled.

Year 2038 problem
http://en.wikipedia.org/wiki/Year_2038_problem

Micha
Jan 22 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Neal | last post by:
I have a MySQL table with a datetime field that stamps the time the user submitted the form. It works and looks like this: $sql = "INSERT INTO table VALUES(....now())"; On another php page to...
0
by: Jim S. | last post by:
I'm having a horrible time simply inserting the date in a MySQL database through a Visual Basic program. I have a table, called "ORDERS"; "ID" is the primary key. I'm trying the insert the date,...
1
by: Kenneth P | last post by:
Hi, I'm trying to do some Custom Paging technique with the datagrid object and with the select command in Sql, thus forcing the server to only select those rows from the database that should be...
5
by: PKin via DotNetMonster.com | last post by:
Hi, I am trying to retreive a record from a MySql table and I get this error telling : Unable to convert MySQL date/time value to System.DateTime I am user the following command:...
0
by: ZoombyWoof | last post by:
Hi. I have ran into a weird thing I just can't find any solution for. I have googled and searched but no luck. The problem is that when I select TIME values from MySQL from python, I get wrong...
12
by: mantrid | last post by:
Hello Can anyone point me in the right direction for the way to read a text file a line at a time and separate the fields on that line and use them as data in an INSERT to add a record to a mysql...
3
by: len | last post by:
Hi All I have started a little pet project to learn python and MySQL. The project involves figuring out all the combinations for a 5 number lottery and storing the data in a MySQL file. The...
0
by: Edwin.Madari | last post by:
-----Original Message----- statement prepared first and executed many times with exectemany - db API http://www.python.org/dev/peps/pep-0249/ inline statemets can be exeucuted only. hope that...
2
by: clintolin | last post by:
My environment php 5.3.0 MySQL Server version: 5.1.37 MySQL client version: 5.1.37 xampp for windows 1.7.2 Windows 7 The Error message I get is this: Array ( => HY000 => 1414 => OUT or...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.