I want to hold the date and time in my database in the following formats: date: DDMMYYYY time: HHMMSS My question is, that I want to display the date in August 13, 2007 format.. would i use mktime to do this or is there a more simple way? Thanks in advance!
I do know how to insert it into the database.. what i'm trying to do is get it from the database and put it in that format.. if i just do date(m,d,Y) it gives today's date. I need it to give the date that's in the database.
There's probably a better way doing it with a single MySQL command, but I don't know better from the top of my head. $time = '27071987'; $time = preg_replace('/(\d{2})(\d{2})(\d{4})/e', 'date("F d, Y", mktime(0, 0, 0, $2, $1, $3))', $time); echo $time; PHP: But personally, I would store the date as UNIX timestamp. It's so much easier to work with.
I agree with nico_swd - a timestamp field would be much easier, or at least standard date and time fields. This allows much more flexibility for SQL queries e.g. adding, subtracting and comparing dates which would be difficult using non-standard storage. Using date(), you have to pass a timestamp along with the formatting options. You could use strtotime() with your storage method, but as it expects something along the lines of YYYY-MM-DD HH:MM:SS you'd need a lot of string manipulation to get it working properly. Disclaimer: Yes, I know strtotime accepts nearly anything, but a quick search suggests apparently not the format Greenmethod specified.
Of course there is a simpler way of achieving this. SELECT DATE_FORMAT(theDate, '%M %D, %Y') FROM myTable Code (markup):
man, i was about to write that! format the dates via mysql instead of php is much easier and faster than adjusting everything in php (and back to mysql-time later again). however, i suggest to use SELECT DATE_FORMAT(theDate, '%M %D, %Y') AS theDate FROM myTable here is a short manual for DATE_FORMAT with common examples: http://www.dan.co.uk/mysql-date-format/
It's not stored in a Date field though, it's stored as a string in the format DDMMYYYY, MySQL Date format is YYYY-MM-DD. I think the general consensus here is that Greenmethod should use a Date / Timestamp field instead
Thanks to everyone for your help! Yea, I do think I am going to use the unix timestamp.. seems like that's what the standard is, basically. I'm working on the code right now.. and if I have any more questions, i'll let ya know! Thanks!
Ok.. i've got it carrying the unix timestamp to my update page, but when it puts it into the database it's all zeros. I think I might have my database settings wrong.. I have it set to timestamp. Should I have it set to something else?
I'd highly recommend storing at as MySQL date because you can utilise all of MySQL's extensive date functions. A timestamp is okay, but you're going to be doing everything at PHP level which is unnecessary. There are only certain actions you should leave up to PHP, such as calculations. Furthermore, the only reason I can really give for all the zeros, apart from the obvious solutions, is that you've got it in sprintf() and you've set the wrong type. NOW() would do just fine. INSERT INTO myTable (date) VALUES (NOW()) Code (markup):
Actually, all the Date functions work on a Timestamp field. It's stored as YYYY-MM-DD HH:MM:SS. Also, by default, a Timestamp is set to NOW() and can be triggered to auto update when the record updated - very useful for logging systems. Edit - your problem is most likely that you're using a Unix Timestamp in PHP which is "the number of seconds elapsed since midnight UTC of January 1, 1970, not counting leap seconds", i.e. a massive number. Refer to the timestamp format above for inserting into MySQL and as I said, if you just want the current time, use NOW(), e.g. INSERT INTO YourTable (YourTimestamp) VALUES (NOW()) Code (markup):
This works, but when I try to display it, using date(), it gives me the wrong date. I am assuming that it gives me the wrong date because date() looks for a unix timestamp, correct? If this is true, can I store the date as a timestamp or datetime, and then use UNIX_TIMESTAMP() to convert the date to a unix timestamp when it loads the page?
If you mean the PHP date function, then yes, but you can convert the MySQL date to PHP date using strtotime(), e.g. if you've retrieved the date from a query and stored it in $date you could use: $strDate = date("d/m/y", strtotime($date)); PHP:
ok.. i got that to work. hopefully this will be the last question I have about this subject.. I am in houston, and would like for it to show CST time. What would be the best way to go about this?
Try this $strDate = date("d/m/y h:i:s", strtotime("$date GMT")); PHP: strtotime() uses the default timezone, so telling it that your database time is stored in GMT and you want to display it in CST should give it the 5 hour offset. I'm not too sure on this one though, so have a play and see if it works as expected.
I got it closer... i decided to do it where i input the date into mysql, so it says now()+18000. That is the correct time difference, but if i calculate it right, the time that is being put into the database is 22 minutes off.. any ideas?
i changed it to 19200 and its still about 10 minutes off.. this is really strange to me.. i could see it being off, but i would think that it would be off a number of hours, not 22 minutes. then i add 1800 seconds (20 minutes) to it and its still off about 10 minutes.. i don't understand.
Try inserting it as DATE_ADD(NOW(), INTERVAL 5 HOUR) Code (markup): Edit: If you're wondering why it didn't work when you added 18000, it's because when you add, it converts the date to an integer - in my test "2007-08-15 20:12:15" becomes 20070815201215. Add 18000 and you get 20070815219215. Convert that back to a date / time and you get 2007-08-15 21:92:15 which is invalid, so it fixes the 92 and becomes 2007-08-15 22:32:15 - not what you wanted at all