[PHP] mysql/php date functions..
hi... can anybody point me to a good/basic tutorial (tested) for php/mysql date functions... basically, i want to store a date/time in a column, and be able to read it, manipulate it, and update the table. i've seen various articles/sample code, but i'm looking for something that i cna pretty much rewrite and use without having to do a lot of initial testing.. if i create the functions myself, i'm going to need to do initial testing to make sure i haven't screwed something up, and i'm on a time crunch!! thanks -bruce [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql/php date functions..
-Original Message- From: bruce [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 11:12 AM To: php-general@lists.php.net Subject: [PHP] mysql/php date functions.. hi... can anybody point me to a good/basic tutorial (tested) for php/mysql date functions... basically, i want to store a date/time in a column, and be able to read it, manipulate it, and update the table. i've seen various articles/sample code, but i'm looking for something that i cna pretty much rewrite and use without having to do a lot of initial testing.. if i create the functions myself, i'm going to need to do initial testing to make sure i haven't screwed something up, and i'm on a time crunch!! Hi Bruce! MySQL and PHP both have extensive built-in date functions that are clearly documented and extraordinarily easy to use. For the vast majority of situations, there is no need to manually write any custom date-handling code. The decision to use MySQL or PHP to manipulate a date for a given instance depends largely on the particulars of that situation. For instance, if you want to increment a MySQL date column by one day, it would likely be better to use the MySQL date functions to do it, because to use PHP, you have to read the date, manipulate it, then write it back, whereas if you use a SQL statement, you can do it with one DB call. See: http://www.php.net/datetime http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html Cheers! JM -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql/php date functions..
Jim Moseby wrote: Hi Bruce! MySQL and PHP both have extensive built-in date functions that are clearly documented and extraordinarily easy to use. For the vast majority of situations, there is no need to manually write any custom date-handling code. The decision to use MySQL or PHP to manipulate a date for a given instance depends largely on the particulars of that situation. For instance, if you want to increment a MySQL date column by one day, it would likely be better to use the MySQL date functions to do it, because to use PHP, you have to read the date, manipulate it, then write it back, whereas if you use a SQL statement, you can do it with one DB call. See: http://www.php.net/datetime http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html Cheers! Just my 0.02 euros. When I need to use dates in PHP *and* MySQL I usually use the UNIX timestamp (seconds since the epoch, that is, the PHP http://php.net/time output). This way I can (quite) easily calculate date differences etc. (usually all the calculations are done in PHP, also when building SQL queries). As I said, just my 0.02 euros... Cheers Silvio -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql/php date functions..
how can i create a mysql sql statement to insert a php 'time()' into mysql? i've got the mysql var 't1, timestamp' but i can't figure out how to do an insert $q = time(); $sql = sprintf(insert into foo (id, ctime) values(%d, %???), $id, $q); can't figure out how to get this to work... if i create a sql statement and run it directly within mysql, --insert into foo (id, ctime) values(2, NOW()); this works.. but i can't figure out how to create the sql using the php time() function and getting the insert from the php app... so what's the basic part i'm missing??!! -bruce -Original Message- From: Silvio Porcellana [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 8:51 AM To: php-general@lists.php.net Subject: Re: [PHP] mysql/php date functions.. Jim Moseby wrote: Hi Bruce! MySQL and PHP both have extensive built-in date functions that are clearly documented and extraordinarily easy to use. For the vast majority of situations, there is no need to manually write any custom date-handling code. The decision to use MySQL or PHP to manipulate a date for a given instance depends largely on the particulars of that situation. For instance, if you want to increment a MySQL date column by one day, it would likely be better to use the MySQL date functions to do it, because to use PHP, you have to read the date, manipulate it, then write it back, whereas if you use a SQL statement, you can do it with one DB call. See: http://www.php.net/datetime http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html Cheers! Just my 0.02 euros. When I need to use dates in PHP *and* MySQL I usually use the UNIX timestamp (seconds since the epoch, that is, the PHP http://php.net/time output). This way I can (quite) easily calculate date differences etc. (usually all the calculations are done in PHP, also when building SQL queries). As I said, just my 0.02 euros... Cheers Silvio -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql/php date functions..
bruce wrote: how can i create a mysql sql statement to insert a php 'time()' into mysql? i've got the mysql var 't1, timestamp' but i can't figure out how to do an insert $q = time(); $sql = sprintf(insert into foo (id, ctime) values(%d, %???), $id, $q); can't figure out how to get this to work... if i create a sql statement and run it directly within mysql, --insert into foo (id, ctime) values(2, NOW()); this works.. but i can't figure out how to create the sql using the php time() function and getting the insert from the php app... so what's the basic part i'm missing??!! -bruce $sql = INSERT INTO db.table ( id, ctime ) VALUES ( . $id . , . time() . ); -- John C. Nichel ÜberGeek KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql/php date functions..
thanks... but that's not it john... i'm not worried about creating the sql_statement in the php... i'm concerned that i can't seem to craft/create a basic sql cmd within mysql to get a value (other than NOW()) to work... if i do (from mysql) -- insert into foo (id, time) values (2, 33), it doesn't work... if i -- insert into foo (id, time) values (2, NOW()), it works!!... my question is why??? -bruce -Original Message- From: John Nichel [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 11:00 AM To: php-general@lists.php.net Subject: Re: [PHP] mysql/php date functions.. bruce wrote: how can i create a mysql sql statement to insert a php 'time()' into mysql? i've got the mysql var 't1, timestamp' but i can't figure out how to do an insert $q = time(); $sql = sprintf(insert into foo (id, ctime) values(%d, %???), $id, $q); can't figure out how to get this to work... if i create a sql statement and run it directly within mysql, --insert into foo (id, ctime) values(2, NOW()); this works.. but i can't figure out how to create the sql using the php time() function and getting the insert from the php app... so what's the basic part i'm missing??!! -bruce $sql = INSERT INTO db.table ( id, ctime ) VALUES ( . $id . , . time() . ); -- John C. Nichel ÜberGeek KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql/php date functions..
along the same way... why can't i see the timestamp designation (ie the double/int) instead of the date-time format when i look at the timestmp var when i do a 'select * from t1' within mysql?? arrgghh! -bruce -Original Message- From: John Nichel [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 11:00 AM To: php-general@lists.php.net Subject: Re: [PHP] mysql/php date functions.. bruce wrote: how can i create a mysql sql statement to insert a php 'time()' into mysql? i've got the mysql var 't1, timestamp' but i can't figure out how to do an insert $q = time(); $sql = sprintf(insert into foo (id, ctime) values(%d, %???), $id, $q); can't figure out how to get this to work... if i create a sql statement and run it directly within mysql, --insert into foo (id, ctime) values(2, NOW()); this works.. but i can't figure out how to create the sql using the php time() function and getting the insert from the php app... so what's the basic part i'm missing??!! -bruce $sql = INSERT INTO db.table ( id, ctime ) VALUES ( . $id . , . time() . ); -- John C. Nichel ÜberGeek KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql/php date functions..
bruce mailto:[EMAIL PROTECTED] on Monday, September 26, 2005 11:13 AM said: i'm concerned that i can't seem to craft/create a basic sql cmd within mysql to get a value (other than NOW()) to work... [snip] my question is why??? MySQL timestamps are different from UNIX timestamps. Chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql/php date functions..
bruce wrote: thanks... but that's not it john... i'm not worried about creating the sql_statement in the php... i'm concerned that i can't seem to craft/create a basic sql cmd within mysql to get a value (other than NOW()) to work... if i do (from mysql) -- insert into foo (id, time) values (2, 33), it doesn't work... if i -- insert into foo (id, time) values (2, NOW()), it works!!... my question is why??? Without knowing the structure of your table, my guess is that the column time is expecting a valid timestamp, and 33 is not a valid MySQL timestamp. -- John C. Nichel ÜberGeek KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql/php date functions..
but that still doesn't explain why i can't slam some value directly into the timestamp var within the mysql tbl... -Original Message- From: Chris W. Parker [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 11:15 AM To: php-general@lists.php.net Subject: RE: [PHP] mysql/php date functions.. bruce mailto:[EMAIL PROTECTED] on Monday, September 26, 2005 11:13 AM said: i'm concerned that i can't seem to craft/create a basic sql cmd within mysql to get a value (other than NOW()) to work... [snip] my question is why??? MySQL timestamps are different from UNIX timestamps. Chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql/php date functions..
john... that appears to be it!! although i would have assumes it would have done a most significant bit fill with 0's... so my question also comes down to .. do i use the php date functions for date/time manipulation.. or do i use the mysql functions any thoughts/suggestions... -bruce -Original Message- From: John Nichel [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 11:19 AM To: php-general@lists.php.net Subject: Re: [PHP] mysql/php date functions.. bruce wrote: thanks... but that's not it john... i'm not worried about creating the sql_statement in the php... i'm concerned that i can't seem to craft/create a basic sql cmd within mysql to get a value (other than NOW()) to work... if i do (from mysql) -- insert into foo (id, time) values (2, 33), it doesn't work... if i -- insert into foo (id, time) values (2, NOW()), it works!!... my question is why??? Without knowing the structure of your table, my guess is that the column time is expecting a valid timestamp, and 33 is not a valid MySQL timestamp. -- John C. Nichel ÜberGeek KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql/php date functions..
bruce wrote: john... that appears to be it!! although i would have assumes it would have done a most significant bit fill with 0's... so my question also comes down to .. do i use the php date functions for date/time manipulation.. or do i use the mysql functions any thoughts/suggestions... -bruce Personal preference I guess. Me, I use UNIX timestamps. -- John C. Nichel ÜberGeek KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql/php date functions..
-Original Message- From: bruce [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 2:46 PM To: 'John Nichel'; php-general@lists.php.net Subject: RE: [PHP] mysql/php date functions.. john... that appears to be it!! although i would have assumes it would have done a most significant bit fill with 0's... so my question also comes down to .. do i use the php date functions for date/time manipulation.. or do i use the mysql functions any thoughts/suggestions... -bruce That depends largely on the situation. For instance, if you want to increment a date in a MySQL table, you would likely do it with a MySQL statement, because to do it in PHP, you need to do a read, then add the day, then a write, (two DB calls). With MySQL, you would just do something like: update `mytable` set 'columnname` date_add(columnname, interval 1 day); One call to the DB instead of two, much more efficient. There are many instances where you would want to use PHP to manipulate dates too, depending on their circumstances. JM -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql/php date functions..
so you play with the time/date vars on the php side, and then simply store them in the mysql tbl as a int(10)... or do you actually store the vars in the mysql timestamp... and then use the mysql date/time functions... ie. how would you do the following... get a date (date1) get a date (date2) store the date1/time in mysql add the date1 + date2 and store the result in mysql read the result from mysql, with the result being in the year/month/date format -- or, would you just get the unix_timestamp representation of the dates, and store the 10 int formats in the mysql tbl. you could then extract/select the date information from the tbls, and do all the date calculations in php... the downside to this is that you'd have to convert all the date information from mysql to a human readable format... thoughts/comments/etc... -bruce -Original Message- From: John Nichel [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 11:48 AM To: php-general@lists.php.net Subject: Re: [PHP] mysql/php date functions.. bruce wrote: john... that appears to be it!! although i would have assumes it would have done a most significant bit fill with 0's... so my question also comes down to .. do i use the php date functions for date/time manipulation.. or do i use the mysql functions any thoughts/suggestions... -bruce Personal preference I guess. Me, I use UNIX timestamps. -- John C. Nichel ÜberGeek KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql/php date functions..
or.. better yet. if i do a php - time(), i get a unix_timestamp var. how do you guys store this in mysql. you can't simply do an insert into a mysql/timestamp var. so how do you convert it? also, once you have the mysql tbl, how do you go from the mysql timestamp var - the php var? thanks bruce -Original Message- From: bruce [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 12:38 PM To: 'John Nichel'; php-general@lists.php.net Subject: RE: [PHP] mysql/php date functions.. so you play with the time/date vars on the php side, and then simply store them in the mysql tbl as a int(10)... or do you actually store the vars in the mysql timestamp... and then use the mysql date/time functions... ie. how would you do the following... get a date (date1) get a date (date2) store the date1/time in mysql add the date1 + date2 and store the result in mysql read the result from mysql, with the result being in the year/month/date format -- or, would you just get the unix_timestamp representation of the dates, and store the 10 int formats in the mysql tbl. you could then extract/select the date information from the tbls, and do all the date calculations in php... the downside to this is that you'd have to convert all the date information from mysql to a human readable format... thoughts/comments/etc... -bruce -Original Message- From: John Nichel [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 11:48 AM To: php-general@lists.php.net Subject: Re: [PHP] mysql/php date functions.. bruce wrote: john... that appears to be it!! although i would have assumes it would have done a most significant bit fill with 0's... so my question also comes down to .. do i use the php date functions for date/time manipulation.. or do i use the mysql functions any thoughts/suggestions... -bruce Personal preference I guess. Me, I use UNIX timestamps. -- John C. Nichel ÜberGeek KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql/php date functions..
bruce wrote: or.. better yet. if i do a php - time(), i get a unix_timestamp var. how do you guys store this in mysql. you can't simply do an insert into a mysql/timestamp var. so how do you convert it? I don't convert it. I store the UNIX timestamp in an INT(11) column. also, once you have the mysql tbl, how do you go from the mysql timestamp var - the php var? Since I keep it in the UNIX timestamp format, there's no converting between the two. -- John C. Nichel ÜberGeek KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql/php date functions..
John Nichel mailto:[EMAIL PROTECTED] on Monday, September 26, 2005 12:43 PM said: I don't convert it. I store the UNIX timestamp in an INT(11) column. This is going to be a basic question I'm sure but why INT and not VARCHAR? Is it simply because a timestamp is a number? Chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql/php date functions..
Read up on mysql date types and functions. You can convert a unix timestamp to a mysql date with the FROM_UNIXTIME() function. If you want to store as unix timestamp, store it in an int field. To use mysql date functions on it, use the FROM_UNIXTIME() function. If you want to store dates in mysql timestamp fields, use in a select statement a) UNIX_TIMESTAMP() or b) DATE_FORMAT() to a) convert to unix timestamp and process with PHP or b) format date for output. http://dev.mysql.com/doc/mysql/en/date-and-time-types.html http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html kgt bruce wrote: or.. better yet. if i do a php - time(), i get a unix_timestamp var. how do you guys store this in mysql. you can't simply do an insert into a mysql/timestamp var. so how do you convert it? also, once you have the mysql tbl, how do you go from the mysql timestamp var - the php var? thanks bruce -Original Message- From: bruce [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 12:38 PM To: 'John Nichel'; php-general@lists.php.net Subject: RE: [PHP] mysql/php date functions.. so you play with the time/date vars on the php side, and then simply store them in the mysql tbl as a int(10)... or do you actually store the vars in the mysql timestamp... and then use the mysql date/time functions... ie. how would you do the following... get a date (date1) get a date (date2) store the date1/time in mysql add the date1 + date2 and store the result in mysql read the result from mysql, with the result being in the year/month/date format -- or, would you just get the unix_timestamp representation of the dates, and store the 10 int formats in the mysql tbl. you could then extract/select the date information from the tbls, and do all the date calculations in php... the downside to this is that you'd have to convert all the date information from mysql to a human readable format... thoughts/comments/etc... -bruce -Original Message- From: John Nichel [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 11:48 AM To: php-general@lists.php.net Subject: Re: [PHP] mysql/php date functions.. bruce wrote: john... that appears to be it!! although i would have assumes it would have done a most significant bit fill with 0's... so my question also comes down to .. do i use the php date functions for date/time manipulation.. or do i use the mysql functions any thoughts/suggestions... -bruce Personal preference I guess. Me, I use UNIX timestamps. -- John C. Nichel ÜberGeek KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql/php date functions..
Chris W. Parker wrote: John Nichel mailto:[EMAIL PROTECTED] on Monday, September 26, 2005 12:43 PM said: I don't convert it. I store the UNIX timestamp in an INT(11) column. This is going to be a basic question I'm sure but why INT and not VARCHAR? Is it simply because a timestamp is a number? Yep. Course, I may be doing it wrong. Wouldn't be the first time. ;) -- John C. Nichel ÜberGeek KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql/php date functions..
Hi The PHP time() function returns the number of seconds since the Unix epoch (Jan 1 1970 midnight) whereas MySQL uses a readable datestamp See here: http://dev.mysql.com/doc/mysql/en/datetime.html for the MySQL date, time and date/time types and here http://uk.php.net/manual/en/function.time.php for time()... instead of using time(), use date() and format it for MySQL http://uk.php.net/manual/en/function.date.php That will allow you to use and in the SQL statements HTH Chrome http://www.chrome.me.uk -Original Message- From: bruce [mailto:[EMAIL PROTECTED] Sent: 26 September 2005 19:46 To: 'John Nichel'; php-general@lists.php.net Subject: RE: [PHP] mysql/php date functions.. john... that appears to be it!! although i would have assumes it would have done a most significant bit fill with 0's... so my question also comes down to .. do i use the php date functions for date/time manipulation.. or do i use the mysql functions any thoughts/suggestions... -bruce -Original Message- From: John Nichel [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 11:19 AM To: php-general@lists.php.net Subject: Re: [PHP] mysql/php date functions.. bruce wrote: thanks... but that's not it john... i'm not worried about creating the sql_statement in the php... i'm concerned that i can't seem to craft/create a basic sql cmd within mysql to get a value (other than NOW()) to work... if i do (from mysql) -- insert into foo (id, time) values (2, 33), it doesn't work... if i -- insert into foo (id, time) values (2, NOW()), it works!!... my question is why??? Without knowing the structure of your table, my guess is that the column time is expecting a valid timestamp, and 33 is not a valid MySQL timestamp. -- John C. Nichel ÜberGeek KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] mysql/php date functions..
Hello Bruce, While the date functions are well documented - http://php.net/date - here is a little example to manipulate the date with php, rather than mysql: // set the new expiry date // DATE FUNCTIONS FOR THE EXPIRY MODULE // first perform date arithmetic $listingExpiry = mktime (0,0,0,date(m)+$monthsGoodFor,date(d)+1,date(Y)); // secondly, format the results for use in the database $expires = date (Y-m-d, $listingExpiry); // the actual update of the database with expires='$expires' ... should be below here The date format to be used in $expires depends on your database column structure. Rob. http://www.globalissa.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
