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 Link to comment https://forums.phpfreaks.com/topic/33181-solved-php-mysql-datetime-fields/ 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 Link to comment https://forums.phpfreaks.com/topic/33181-solved-php-mysql-datetime-fields/#findComment-154858 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 Link to comment https://forums.phpfreaks.com/topic/33181-solved-php-mysql-datetime-fields/#findComment-154863 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 :) Link to comment https://forums.phpfreaks.com/topic/33181-solved-php-mysql-datetime-fields/#findComment-154865 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??????? Link to comment https://forums.phpfreaks.com/topic/33181-solved-php-mysql-datetime-fields/#findComment-154867 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. Link to comment https://forums.phpfreaks.com/topic/33181-solved-php-mysql-datetime-fields/#findComment-154874 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??? Link to comment https://forums.phpfreaks.com/topic/33181-solved-php-mysql-datetime-fields/#findComment-154879 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 Link to comment https://forums.phpfreaks.com/topic/33181-solved-php-mysql-datetime-fields/#findComment-154884 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????.......!!! Link to comment https://forums.phpfreaks.com/topic/33181-solved-php-mysql-datetime-fields/#findComment-154886 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] Link to comment https://forums.phpfreaks.com/topic/33181-solved-php-mysql-datetime-fields/#findComment-154891 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! Link to comment https://forums.phpfreaks.com/topic/33181-solved-php-mysql-datetime-fields/#findComment-154892 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 Link to comment https://forums.phpfreaks.com/topic/33181-solved-php-mysql-datetime-fields/#findComment-154894 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.