...

View Full Version : time format help



ptmuldoon
03-13-2007, 09:23 PM
I have datetime field in a table that stores the information as. I understand how to query the field.

And if I echo the variable $time, I will get

2007-03-13 07:27:00

But I'd like to echo that into anther format, perhaps

MM/DD : HH/MM

How would I convert the $time variable to echo into a different format?

CFMaBiSmAd
03-13-2007, 09:27 PM
Use the mysql date_format() function in your query. Information on the date_format() function and some examples are at this link - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

ptmuldoon
03-13-2007, 10:22 PM
I would have thought there be an easier way than having to manipulate the sql queries.

I was hoping to extract the date and time out of the table, and then be able to manipulate the info into something like:

echo $time;
$time = date("m/d/y", $time);
echo '<br />';
echo $time;

Unfortunately, in that example, I get 12/31/69 instead of the 3/13/07

CFMaBiSmAd
03-13-2007, 10:31 PM
The PHP date(...) function requires a unix timestamp. Your starting date format, 2007-03-13 07:27:00, is not a unix timestamp. It requires multiple statements in PHP to accomplish what you can do within a single statement in the query. I method I mentioned is the most efficient way of doing this.

ptmuldoon
03-13-2007, 10:44 PM
Gotcha.

Thanks

I may just look to convert the dates to unix the db, as I think that may be the best approach for the long term.

Fumigator
03-13-2007, 11:03 PM
That's taking a big step backwards in my opinion, it's much better to store a date or time or date/time in the database using the data types provided by the database. It's cake to format a datetime field to whatever you want, it's a pain in the arse to format an integer.

ptmuldoon
03-14-2007, 02:39 AM
Really?

I would have thought simply using the php date function to convert the unix time into any format would be the easiest.

So I can easily query out date and time of: 2007-03-13 07:27:00

and turn that into a simply MM/DD : HH/Min format?

Can you have one query, and than place it into multiple formats?

Fumigator
03-14-2007, 03:51 AM
Using datetime data types gives you more than just ease of formatting-- you can perform comparisons and date operations (add 1 day, 5 weeks, 3 months, etc) all within a query. There really is no compelling reason not to use datetime data types.

You can query multiple formats like this:


$query = "SELECT
date_format(date_field, '%m/%d : %l/%i') as format1,
date_format(date_field, '%M %D, %Y in the year of our lord, at %r) as format2
FROM my_table";


Read up on date/time functions in the MySQL manual here (http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html).

CFMaBiSmAd
03-14-2007, 04:14 AM
There seems to be an echo in here...

aedrin
03-14-2007, 04:34 PM
The PHP date(...) function requires a unix timestamp. Your starting date format, 2007-03-13 07:27:00, is not a unix timestamp. It requires multiple statements in PHP to accomplish what you can do within a single statement in the query. I method I mentioned is the most efficient way of doing this.

I disagree, I believe it's mostly preference.

It doesn't take multiple statements.



$result = query("SELECT * FROM table");

while ($row = get_row($result)) {
echo "date=" . date("Y/m/d h:i", $row['date']);
}


You either place the burden on SQL, or on PHP. I prefer PHP, as a database system's purpose is to store data. Not doing fancy things with it. This can be neglectable often, but sometimes when you have a high traffic database. Would you want to lose CPU time on something as silly as formatting a string?

You don't have to worry about formatting issues either when inserting into the database. Doing date math is easy (need to subtract a day? subtract 86400). PHP provides all the functions you need to construct dates from a unix timestamp.

There are pros and cons to both methods. In the end, it is whichever you are comfortable with.

CFMaBiSmAd
03-14-2007, 04:52 PM
His existing database column is a datetime - 2007-03-13 07:27:00. The PHP date(...) function won't work in this case, which is why the code in the first (Edit: third) post, using date(...) does not produce the correct results...

aedrin
03-14-2007, 04:56 PM
But when the user is considering switching to a unix timestamp, it doesn't matter what is there currently.

Some situations benefit from switching to another method.

CFMaBiSmAd
03-14-2007, 05:13 PM
Re-read this -
That's taking a big step backwards in my opinion, it's much better to store a date or time or date/time in the database using the data types provided by the database. It's cake to format a datetime field to whatever you want, it's a pain in the arse to format an integer.Also, the UNIX timestamp, brought to you by the same programmers that gave us the Y2K problem a few years back, has a serious Y2.038K problem (and a Y1.970K problem) that makes it problematic for real world things like storing and doing age calculations on DOB.

Fumigator
03-14-2007, 05:19 PM
You lose date comparison when you store a date as a char type, and you add unnecessary complexity when you store a date as a unix timestamp integer.


WHERE date1 between '2006-04-01' and '2007-02-01'


That's impossible to do with a char field, and requires a conversion with an integer. What's the point?

Not to mention the issues with pre-1969 dates...

EDIT: I didn't know about that Y2.038K problem... hey that's pretty cool, I cashed in on the Y2K problem, I can now look forward to more big contracting $$$ for Y2.038K :D

aedrin
03-14-2007, 08:31 PM
Re-read this -

How is this



$echo date("Y/m/d", time());


More difficult than formatting in the query itself?

You basically move the format string from PHP to SQL, I fail to see the improvement.


Also, the UNIX timestamp, brought to you by the same programmers that gave us the Y2K problem a few years back, has a serious Y2.038K problem (and a Y1.970K problem) that makes it problematic for real world things like storing and doing age calculations on DOB.

I'm sure servers will move to 64bit by the year 2038. Just to clarify, that is 18446744073709551616 seconds since 1970. Which means that it'll last until the year 584942417355. Should we still have a sun, I'd hope the application is no longer in use.


and you add unnecessary complexity when you store a date as a unix timestamp integer.

It's a different 'complexity' from datetime.

It's easy for me to do some calculations with times, for instance when displaying a complicated calendar.

Like I said before, both have their pros and cons. Neither is the right way. I'm always open to new methods, but I haven't seen any convincing pros that would get me to switch.

EDIT:

How about portability? I know I can switch my date methods/calculations over to another DB provider (Oracle, PGSQL, etc.) without problems. I don't think using Datetime would allow the same effortless transport.

Fumigator
03-14-2007, 08:51 PM
I'm sure servers will move to 64bit by the year 2038.

That sounds hauntingly familiar... programmers in 1979 were saying the very same thing about systems that stored 2-digit years. They could not imagine that those current systems would still be in use 21 years later... but yet there I was, furiously making changes in December 1999!

Granted, the life cycle for computer hardware is crazy short, so I would probably agree that the world will be 64 (or even 128) bit in 21 years. My flying car better be using a 128bit system! :p

Fun reading:

http://www.deepsky.com/~merovech/2038.html

Fumigator
03-14-2007, 09:02 PM
This is an interesting discussion, btw...


How about portability? I know I can switch my date methods/calculations over to another DB provider (Oracle, PGSQL, etc.) without problems. I don't think using Datetime would allow the same effortless transport.

That is a very good point. One particular project I was on required conversion from DB2 to Oracle and the biggest headaches we had were related to date and timestamp fields. The whole project went pretty smoothly though so I guess it's yet another trade-off. The ability to do a "SELECT * FROM table WHERE month(date_field) = 2" (just one of many many examples) is worth the obstacles of portability in my opinion.

Oh hey, here's another pro for date types: forced data validation. With the unfortunate exception of MySQL, other RDBMS's require a valid date in a date field, so you need not worry about whether or not your date calculation is going to fail due to invalid data. (I still can't get over MySQL's acceptance of invalid dates, what's up with that?)

Fumigator
03-15-2007, 01:29 AM
http://codingforums.com/showthread.php?t=109852

Here's an example of something that would be a real pain to do on an INT field...



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum