PC Nerd Posted January 7, 2007 Share Posted January 7, 2007 when storing a date of time in a MySQL database..... how does it store the data and how would i retrive the data, in terms of ordering the results but the date then time???eg a personal messageing system where you may get multiple messages in one day so you need to know the send time to order the reults correctly Quote Link to comment Share on other sites More sharing options...
chronister Posted January 7, 2007 Share Posted January 7, 2007 Though date and time functions in php are a bit daunting at first, the best way I found is to use the unix timestamp by using [code]time();[/code] and then convert that timestamp into a readable date with [code]date("m/d/y, $timstamp_var_here")[/code]That way, with 1 string, you have the complete date and time a pm was sent.This also makes ordering the results easy, as all you have to do is in your query add [code]SELECT * FROM personal_messages ORDER BY timestamp_field DESC [/code]The key there is the ORDER BY XXXXX DESC, which gives you the data newest to oldest. If you want Oldest to newest, use ORDER BY XXXXXX ASCEDIT: You can also use the mktime() function to create complete hour,minute,second, day, month, year strings from the timestamp. Check out the manual for info on date/time functions..[url=http://us2.php.net/manual/en/ref.datetime.php]http://us2.php.net/manual/en/ref.datetime.php[/url]The table of contents gives a list of the functions related to date time manipulation in php.Nate Quote Link to comment Share on other sites More sharing options...
PC Nerd Posted January 7, 2007 Author Share Posted January 7, 2007 so does the datetime field type actually store a timestamp, in which case all id do is use the format thingy to turn it into the correct format???could someone please explain this o me in more detail Quote Link to comment Share on other sites More sharing options...
chronister Posted January 7, 2007 Share Posted January 7, 2007 Yes, it would store it in a unix timestamp e.g. 1168153768To turn this into a human readable date simply use...[code]date("h:i:s m/d/Y", 1168153768);[/code]produces 01:09:28 01/07/2007Study the manual for this stuff, its complicated at first... but the key is to research the manual. Again the link is [url=http://us2.php.net/manual/en/ref.datetime.php]http://us2.php.net/manual/en/ref.datetime.php[/url]The table of contents at the bottom has all the functions. Read, Study, Learn :) Quote Link to comment Share on other sites More sharing options...
PC Nerd Posted January 7, 2007 Author Share Posted January 7, 2007 ok, and when using date(); to retreive the date just simply to get the current date or time..... does it just use the time thats set on the server or somputer running the page, or is the actual correct timeeg if my c omputer is set to midday on the 1/1/07, but the correct time is 1:45 pm 3/1/2007, which time will date() retreive??????? Quote Link to comment Share on other sites More sharing options...
chronister Posted January 7, 2007 Share Posted January 7, 2007 time(); will return the current timestamp according to what time is set on the server. If the server is set to an incorrect time, then the time() function will return the incorrect time. To use date, you specify which format you want the results to be displayed in (the manual has an extensive table on this for the date() function). You can give the date() function an argument as I did in the example to return a readable date from a timestamp, or you can simply leave the argument out to produce the current time and date.[code]date("h:i:s m/d/Y")[/code]returns 01:37:59 01/07/2007 when I ran it at that particular time... I simply used this line[code]<?=date("h:i:s m/d/Y") ?>[/code]which is the shorthand for [code]<?php echo date("h:i:s m/d/Y") ?>[/code]Echoing the current time/date using the aforementioned line will update every second if you keep hitting refresh. Quote Link to comment Share on other sites More sharing options...
PC Nerd Posted January 7, 2007 Author Share Posted January 7, 2007 ok, so what the time being like GMT or UTC or whatever, is the format also decided by the server or is it always GMT??? Quote Link to comment Share on other sites More sharing options...
chronister Posted January 7, 2007 Share Posted January 7, 2007 the timestamp is set by the server's time. No matter where, or when the server dictates what the timestamp returns. (Other Users: If I am wrong on this one, please feel free to let me know, because I am not POSITIVE on this, but fairly sure.)I was testing a timeclock system and wanted a timestamp for last month and last year, I did not feel like using a mktime function and generating the unix string for each one, and manually inserting into a db, so I simply changed the time on my testing server, and ran my tests, changed again, ran the test, and then changed back. When I was done I had a punch in / punch out for dates in last month and last year. (Before someone responds with why didn't you just....., I know I could have coded it to do it, but this was more fun ;D )Nate Quote Link to comment Share on other sites More sharing options...
PC Nerd Posted January 7, 2007 Author Share Posted January 7, 2007 YES but what about the time zones??? are they set by the computer of something else???? like it my server is running on gmt time, will it change the time stamp to the correct time for the user, like set in their browser, or is it just retrived as GMT, and i have to change it to the correct timezone????.......!!! Quote Link to comment Share on other sites More sharing options...
chronister Posted January 7, 2007 Share Posted January 7, 2007 I am not sure about that one, Again... The manual will probably answer this question for you [url=http://us3.php.net/manual/en/ref.datetime.php]http://us3.php.net/manual/en/ref.datetime.php[/url]From The Manual... Found it from just getting the link to the page again.[quote]InstallationThere is no installation needed to use these functions; they are part of the PHP core. [b]Note: The latest version of the timezone database can be installed via PECL's timezonedb. For Windows users, a pre-compiled DLL can be downloaded from the PECL4Win site: php_timezonedb.dll.[/b] [/quote] Quote Link to comment Share on other sites More sharing options...
chronister Posted January 7, 2007 Share Posted January 7, 2007 From The Manual...[url=http://us3.php.net/manual/en/function.date-default-timezone-get.php]http://us3.php.net/manual/en/function.date-default-timezone-get.php[/url][url=http://us3.php.net/manual/en/function.date-default-timezone-set.php]http://us3.php.net/manual/en/function.date-default-timezone-set.php[/url][url=http://us3.php.net/manual/en/function.timezone-abbreviations-list.php]http://us3.php.net/manual/en/function.timezone-abbreviations-list.php[/url][url=http://us3.php.net/manual/en/function.timezone-identifiers-list.php]http://us3.php.net/manual/en/function.timezone-identifiers-list.php[/url]30 seconds in the manual gave me these links.... So..... check the manual and you can learn all about timezone functions there! Quote Link to comment Share on other sites More sharing options...
PC Nerd Posted January 7, 2007 Author Share Posted January 7, 2007 thanks, i think that helped, but ill let you guys know if it didntthanks a heap everyone Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.