Inserting date in MySQL

[kdshah]

One more thing which I want to do in addition to what i mentioned in the previous post is:

retrieve the current time using some PHP function and then inserting this value in a mysql table. If someone suggests using a timestamp, then I would like to know how could I update this stored value of time (say, i want to update this value at some other time).

thanks,
Kunal

 

[GiffordS]

Working backwards.....

You can access the current time in PHP through the Date() function. There are about 25 different arguements that determine what is returned. Check the manual for specifics as it is a lot to type out here. A basic set would look like this:

Date(g:i a);

Would return the current time as something like "3:31 pm".

Capitalizing the g, Date(G:i a);, would add a leading zero to numbers one thru ten returning "03:31 pm".

Capitalizing the a, Date G:i A);, would capitalize the am/pm returning "03:31 PM".

There are arguements for everything from unix time to day, date, month, year, etc. To add an hour all you would need to do is split the string and capture the first two characters returned, in this case "03", add 1 and put the string back together. A simple conditional loop would be needed to ensure that if the number returned is equal to twelve then you reset to "01" and change the am/pm rather than adding 1.

As for your first question, if I understand it correctly, you would not really be able to use a Date type. It automatically stores the date in a YYYY-MM-DD format. Besides, you're receiving the variable as a string, so why not just continue treating it as a string? PHP doesn't limit what you can do with it. You can do a little string manipulation to strip the "/" or "-" from the variable, and by breaking apart the string put it in any order you like. From there you can even add or subtract to any date you want.

 

[kdshah]

giffords,

thanks for your reply. well, let me layout my problem in some other way.

I want to have a date (could be of date type or some other type) field in mysql database which would store an expiry date. This date is entered by the user and is sent to the PHP script as a querystring of the form mm/dd/yyyy and this value is then to be stored in the mysql table.

Now, I would like to add 30 days to the current time if this value is not entered by the user (sort of default value).

Keeping this in mind, what would be the ideal datatype that i should be using in Mysql database. I'LL NEED TO COMPARE THIS VALUE WITH THE CURRENT DATE everytime I lookup something in that table, TO CHECK WHETHER THE EXPIRY DATE HAS PASSED .

Guide me on this.

Thanks,
Kunal

 

[sleipnir214]

You can update a timestamp field just like any other field. It's just that if you do not include that field in an update or insert statement, MySQL will automatically update the field for you.

Give a table named foo with the following structure:

[tt]

+-------+---------------------+
| Field | Type                |
+-------+---------------------+
| id    | tinyint(3) unsigned |
| stamp | timestamp(14)       |
| data  | varchar(10)         |
+-------+---------------------+

[/tt]

And the following data:

[tt]

+----+----------------+-------+
| id | stamp          | data  |
+----+----------------+-------+
|  1 | 20021015145638 | one   |
|  2 | 20021015145639 | two   |
|  3 | 20021015145640 | three |
|  4 | 20021015145641 | four  |
+----+----------------+-------+

[/tt]

Then the statement

[tt]

UPDATE foo SET stamp = '2002-10-16 13:00:00' WHERE id = 2

[/tt]

Puts the table in the following state:

[tt]

+----+----------------+-------+
| id | stamp          | data  |
+----+----------------+-------+
|  1 | 20021015145638 | one   |
|  2 | 20021016130000 | two   |
|  3 | 20021015145640 | three |
|  4 | 20021015145641 | four  |
+----+----------------+-------+

[/tt] ______________________________________________________________________
TANSTAAFL!