newbie myphpadmin /database question

fxpepper

If I choose date as the column type, does it matter what format i put the date in? I am trying to save the date in the following format:

6/28/2011 6pm

but it's not saving this way. It's saving as 0000-00-00 00:00:00

or is this issue not related?

thanks!

bradgrafelman

fxpepper wrote:
If I choose date as the column type, does it matter what format i put the date in?

Definitely. The MySQL manual page 10.3.1. The DATETIME, DATE, and TIMESTAMP Types explains which formats are valid (look at the bulleted list preceded by the text "You can specify DATETIME, DATE, and TIMESTAMP values using any of a common set of formats:").

fxpepper

bradgrafelman;10981910 wrote:
Definitely. The MySQL manual page 10.3.1. The DATETIME, DATE, and TIMESTAMP Types explains which formats are valid (look at the bulleted list preceded by the text "You can specify DATETIME, DATE, and TIMESTAMP values using any of a common set of formats:").

Hey - thanks for the info.

So if i am understanding this correctly, I need to store it as a mysql (in YYYY-DD-MM or YYYY-MM-DD HH:MM:SS) value - there is no way around this - it actually needs to be saved this way...
(this is why I was getting a 0 values b/c it was illegal value before?)

and then converted/formatted when it's returned using php...is that right?

bradgrafelman

fxpepper wrote:
I need to store it as a mysql (in YYYY-DD-MM or YYYY-MM-DD HH:MM:SS) value - there is no way around this - it actually needs to be saved this way

Yes, that is way in which DATE/DATETIME/TIMESTAMP values will be stored. In order for MySQL to be able to treat such values as dates and times, it must know how to read and parse the data. To do that, the data must be presented in a specific format.

fxpepper wrote:
and then converted/formatted when it's returned using php

Not necessarily with PHP (MySQL has plenty of its own Date and Time Functions, y'know..), but yes, you can format the date/time values into whatever format you desire for display purposes.

fxpepper

Thanks for all the help 🙂

fxpepper

is this related to the mysql i mentioned earlier:

I tjhink I am close now. I am working on the following - which half works:

$value = empty($value) ? date("m/d/Y g:ia", strtotime(now)) : date("m/d/Y g:ia", strtotime($value)) ;

<input name="

<?php echo $name; ?>

" type="text"[/code]" id="

<?php echo $css_id; ?>

" value="

<?php echo $value;  ?>

" />

the "now" time works but the 2nd date is changing to a NOv 30- 0001 date...any feedback on that? I am not sure if it's related to the above issue or it's with the date format. But since the "now" once is working, I am thinking it's a dating format issue...right?

EDIT ** I see that this is being returned b/c of the mysql 0 value...
i';m sorry but this is a little confusing...
can you give me any tips for setting this up?

the value comes in as 06/25/2011 6pm for example...set up with a datepicker...
so then I have to convert that before the form is saved to a mysql value? should I use a hidden field? and then convert it back. I know php a little bit and mysql hardly at all at this point so I would rather user php to format this...
any direction is appreciated

thanks again

bradgrafelman

fxpepper wrote:
the value comes in as 06/25/2011 6pm for example...set up with a datepicker...

Doesn't the datepicker give you options to change the format of the form value it uses for the selected date and time?

fxpepper

bradgrafelman;10981925 wrote:
Doesn't the datepicker give you options to change the format of the form value it uses for the selected date and time?

yes, I was actually just looking at that...and that works but the whole point of the datepicker was to show it in a nice format for the user...

bradgrafelman

I guess it depends on the datepicker and how you expect it to be used. I would think that a good datepicker would give you the option of displaying the date in one format while sending it in the form as another. In addition, if the date picker inserts the text values after the user selects the date, then why would it matter what the text values are as long as the user can use the datepicker interface instead?

Finally, note that MySQL does have a STR_TO_DATE() function you can use.

fxpepper

bradgrafelman;10981927 wrote:
I guess it depends on the datepicker and how you expect it to be used. I would think that a good datepicker would give you the option of displaying the date in one format while sending it in the form as another.[] In addition, if the date picker inserts the text values after the user selects the date

I am reading up on that now. it does have an altFormat but it's for another text field so i;m thinking this may work but I need to see how to set this up...

then why would it matter what the text values are as long as the user can use the datepicker interface instead?

true..but i just wanted it to show this format when it was selected too.

Finally, note that MySQL does have a STR_TO_DATE() function you can use.

I definitely need to look into learning mysql but I think I would rather do this with php or javascript if possible. Just b/c that's a whole new field i need to learn vs knowing the basics...

bradgrafelman

fxpepper wrote:
that's a whole new field i need to learn vs knowing the basics...

I would think a simple call to a built-in function would definitely be considered part of the 'basics'. 😉

fxpepper

bradgrafelman;10981929 wrote:
I would think a simple call to a built-in function would definitely be considered part of the 'basics'. 😉

not sure what you mean...

do you mean the datapicker?

maybe i over qualified myself then...b/c I couldn't get it to work how I wanted... : (

fxpepper

bradgrafelman;10981927 wrote:
Finally, note that MySQL does have a STR_TO_DATE() function you can use.

more on this...since I am still trying to perfect this....

if i use this function, it will only work on dates already in the database. new values, can't use this function (that are in limbo), right? is there any way to fix this?

I tried changing the value on submit but that didn't work either...

it's really annoying (inho) that they don't allow these alt date formats...

thanks

bradgrafelman

fxpepper wrote:
new values, can't use this function

Why do you say that?

Functions in MySQL (both user-defined and built-in) behave quite similarly to those in PHP in that they simply return values; you can use them anywhere (such as in an INSERT query).

Instead of using a simple string in the VALUES() section, for example, use the return value of that function instead.

EDIT:

fxpepper wrote:
it's really annoying (inho) that they don't allow these alt date formats...

It would be far, far more annoying (and worse - prone to errors) if they did allow anything other than a single format.

For example, say you query my DB and it spits out "01.02.03". Can you tell me, which of these values did I store: January 2nd 2003, Februrary 1st 2003, or 2 minutes and 3 seconds past 1am (or wait.. maybe it was 1pm..)?

The answer is you can't, unless the DB forces me to use a single standard format that we can both agree on (since we don't have a choice).

fxpepper

bradgrafelman;10981982 wrote:
Why do you say that?

b/c I was thinking if I have an input field where the user is selecting the new date - it hasn't hit the db yet. I was thinking in terms of SELECTing it I guess...
or are you saying INSERT with this value using this function???

I think I will need to do some tutorials on mysql. unfortunately, I am not sure I understand...

EDIT: It would be far, far more annoying (and worse - prone to errors) if they did allow anything other than a single format.

For example, say you query my DB and it spits out "01.02.03". Can you tell me, which of these values did I store: January 2nd 2003, Februrary 1st 2003, or 2 minutes and 3 seconds past 1am (or wait.. maybe it was 1pm..)?

The answer is you can't, unless the DB forces me to use a single standard format that we can both agree on (since we don't have a choice).

I do see what you're saying...

well thanks again for all the help

bradgrafelman

fxpepper wrote:
are you saying INSERT with this value using this function???

Well yes, since that's what you're trying to do.

Obviously, this:

INSERT INTO myTable (date_col) VALUES ('6/28/2011 6pm')

is going to fail, since the date isn't in the right format. Therefore, rather than using a simple string in the VALUES() section, simply replace it with a call to STR_TO_DATE() instead.

It's the same concept as in PHP... if you've got this:

function foo() { return "Hello World!"; }

echo "My message...";

and wanted to use the return value of foo() in place of "My message..." what do you do? Simply replace the string with a call to the function, e.g.

echo foo();

fxpepper

bradgrafelman;10981989 wrote:
Obviously, this:
INSERT INTO myTable (date_col) VALUES ('6/28/2011 6pm')
is going to fail, since the date isn't in the right format. Therefore, rather than using a simple string in the VALUES() section, simply replace it with a call to STR_TO_DATE() instead.

right...okay I see...
so something like this:

$date = date('Y-m-d H:i:s',$datetime);
INSERT INTO myTable (date_col) VALUES ($value);

INSERT INTO INTO myTable date_time_col VALUES (str_to_date($date,"%m/%d/%Y %h:%i"))