MySQL Date Settings

In my db table I have a field named “date” but no matter what I set
the “Type” to I get an error about an invalid value. Plus it auto-
populates the text-field with 0000-00-00 and I can’t get rid of it,
regardless of the settings. What are the specific table settings for a
date field?

Todd


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

There are a few different date fields in MySQL.

date (defaults to YYYY-MM-DD)
datetime (defaults to YYYY-MM-DD HH:MM:SS)
time (defaults to HH:MM:SS)
timestamp (same as datetime, but with an implicit value of NOW())
year (defaults to YYYY)

When you say that it auto-populates the text field with that value, is
there another value you would like better? If you’re working in MAR,
you have some goodies you can use to pre-fill the fields of a new
record.

$foo = MyActiveRecord::Create('foos');
$foo->date = MyActiveRecord::DbDate(); //today
or
$foo->date =  MyActiveRecord::DbDate(strtotime('+2 day')); //day after  
tomorrow

$out = render_partial('date_form',$foo);

Now your input field will be pre-filled with the current date, and if
you save it, that value will persist. You could also blank the field
entirely by changing it to:

$foo->date = '';

Walter

On Aug 18, 2009, at 1:23 PM, Todd wrote:

In my db table I have a field named “date” but no matter what I set
the “Type” to I get an error about an invalid value. Plus it auto-
populates the text-field with 0000-00-00 and I can’t get rid of it,
regardless of the settings. What are the specific table settings for
a date field?

Todd


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

The field does not need to auto-populate. I was going to allow the
user to format the date however they like so I figured setting it as
varchar 255 would be the way to do this but still the text field is
filled in with 0000-00-00.

Todd


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

When you create a new field with the type of date or datetime, MySQL
will fill in the default format in 0’s for you. If you go back and
change the field type to varchar, the default is not altered, since a
bunch of zeros is perfectly valid for that field type. All you have to
do is change the default to NULL or the empty string and you should
get the result you are looking for.

Walter

On Aug 18, 2009, at 1:55 PM, Todd wrote:

The field does not need to auto-populate. I was going to allow the
user to format the date however they like so I figured setting it as
varchar 255 would be the way to do this but still the text field is
filled in with 0000-00-00.


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

I was going to allow the user to format the date however they like

Uh-oh… surely that way lies madness? If you ever want to do
anything with that date data it might be better to offer them some
kind of JS-based month/day picker that feeds specific values back
when the form is submitted.

k


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Good point.

Todd

On Aug 18, 2009, at 1:24 PM, Keith Martin wrote:

Uh-oh… surely that way lies madness? If you ever want to do
anything with that date data it might be better to offer them some
kind of JS-based month/day picker that feeds specific values back
when the form is submitted.


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

http://www.actionsforge.com/actions/view/109-calendarview

very easy to add.

Walter

On Aug 18, 2009, at 2:30 PM, Todd wrote:

Good point.

Todd

On Aug 18, 2009, at 1:24 PM, Keith Martin wrote:

Uh-oh… surely that way lies madness? If you ever want to do
anything with that date data it might be better to offer them some
kind of JS-based month/day picker that feeds specific values back
when the form is submitted.


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Not sure if this helps but I allow the user to select the date format
on a dynamic site although I save the selected date setting the person
selects and then a script formats the date and shows it relative to
the users selection, so the date is the same in the database and the
users selection is read from the database and the date set relative to
their selection. This of course is ok for a members area or if the
user has to login but wouldn’t really work for people who do not log
in unless a session or cookie is set.

HTH

On Aug 18, 2009, at 8:24 PM, Keith Martin wrote:

I was going to allow the user to format the date however they like

Uh-oh… surely that way lies madness? If you ever want to do
anything with that date data it might be better to offer them some
kind of JS-based month/day picker that feeds specific values back
when the form is submitted.


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

It works. Now if I want to reformat the default date to something like,

Sunday, September 20, 2008

would I add the following

$date_format = date(’%W, %M %e, %Y’);
return $date_format;

Todd

On Aug 18, 2009, at 12:47 PM, Walter Lee Davis wrote:

$foo->date = MyActiveRecord::DbDate(); //today

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

As long as you don’t expect to store that value in a Date column in
the database, then that’s almost right. If you do want to continue
storing the date in the database in a native Date or DateTime format,
then you will need to translate for display, interpret any changes
that the user makes back into ISO date[time] format, and store it
again. This is why I like CalendarView so well – it handles the
translation and makes it difficult for an end-user to enter something
that the computer will choke on.

The date() function expects a PHP time() value, which is the number of
seconds since January 1, 1970. MAR includes a helper method to convert
a MySQL Date into a PHP time value, and from there you get the
formatted string.

$timestamp = $foo->get_timestamp('date');
$formatted = date('%W, %M %e, %Y',$timestamp);

Walter

On Aug 22, 2009, at 2:12 PM, Todd wrote:

It works. Now if I want to reformat the default date to something
like,

Sunday, September 20, 2008

would I add the following

$date_format = date(’%W, %M %e, %Y’);
return $date_format;

Todd

On Aug 18, 2009, at 12:47 PM, Walter Lee Davis wrote:

$foo->date = MyActiveRecord::DbDate(); //today

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Yes, I do want to store the date and translate it into a more readable
format. Am I close with this?

$foo->date = MyActiveRecord::DbDate(); //today
$timestamp = $foo->get_timestamp('date');
$formatted = date('%W, %M %e, %Y',$timestamp);
$date_format = date('%W, %M %e, %Y');
return $date_format;

Btw, CalendarView is nice but I’m using moo.

Todd

On Aug 22, 2009, at 1:29 PM, Walter Lee Davis wrote:

$timestamp = $foo->get_timestamp('date');
$formatted = date('%W, %M %e, %Y',$timestamp);

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Looking back over this, I’m wondering what the % signs are doing in
there at all. Are these the PHP date() format or something else?

Also, the last three lines are contradicting themselves. $formatted is
what you want to return, the next two lines basically do the same
thing again, but without referring to the timestamp that you generated
in MAR. If you leave off the second argument to date(), it acts as if
you want to have the formatted version of right now.

And if all you want to get from the function is the current date,
formatted as you indicate, you could simply do this"

return date('l, F j, Y',time()); //that's a lower-case L for the first  
format token

See what happens on your server if you run the following snippet of
PHP all by itself:

<?php
$now = date('l, F j, Y',time());
print $now .  "n";
$timestamp = strtotime($now);
print date('Y-M-D',$timestamp);
?>

If this works, then you should see the same date formatted two
different ways. If it doesn’t, then the second one will be something
besides the current date.

strtotime() is pretty flexible about what form it gets a date in, and
usually does a yeoman job of converting a “natural language” input
into a timestamp. But I’m not sure if it can go all the way as you do
here with the day of the week and such. That might throw it off.

All in all, this is pretty fragile. If you allow the end user to edit
a natural language version of the date, you may find it nearly
impossible to trap all possible errors and convert that “date” into
something the computer can reckon with.

The reason you want to use real dates in your database is because
MySQL can do powerful things with sorting them and deciding what range
of records fall between two other dates and such. It’s quite deep. But
if you let the user mess with it at that level, it’s going to be
difficult at best to figure out if what you get out of the edit is
actually the real date or not. strtotime() will probably fail and
produce a time of -1, which date() will turn into December 31, 1969.

Many systems that allow date editing use individual pickers for each
segment of the date, and use JavaScript to force those pickers to
conform, so you would never get February 30th, for example. For simple
systems, I just show the ISO date, as it’s unambiguous, and trust the
user. For something public-facing you want to lock it down more
completely.

And then there’s this: MooTools DatePicker Example
which is the first hit under mootools date picker in the google.

Walter

On Aug 22, 2009, at 2:48 PM, Todd wrote:

Yes, I do want to store the date and translate it into a more
readable format. Am I close with this?

$foo->date = MyActiveRecord::DbDate(); //today
$timestamp = $foo->get_timestamp('date');
$formatted = date('%W, %M %e, %Y',$timestamp);
$date_format = date('%W, %M %e, %Y');
return $date_format;

Btw, CalendarView is nice but I’m using moo.

Todd

On Aug 22, 2009, at 1:29 PM, Walter Lee Davis wrote:

$timestamp = $foo->get_timestamp('date');
$formatted = date('%W, %M %e, %Y',$timestamp);

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

OK, I’ll forget about the fancy formatting. But what about simply
changing the default to,

mm-dd-YYYY

Is that still potentially problematic?

Todd


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

As long as you create a translator, it’s fine.

class foo extends ActiveRecord{
	function get_date(){
		$date = preg_split('/-/',$this->date);
		//output in a different order
		return $date[1] . '-' . $date[2] . '-' . $date[0];
	}
	function set_date($strDate){
		$date = preg_split('/-/',$strDate);
		$this->date = $date[2] . '-' . $date[0] . '-' . $date[1];
	}
}

Now you would need to use $foo->get_date() to populate the date in
your various views rather than accessing the date property directly,
and when you update in an edit controller you would use set_date to
set the input value.

if(isset($_POST['save'])){
	$_POST = clean($_POST);
	$foo->populate($_POST);
	$foo->date = $foo->get_date($_POST['date']);
	$foo->save();
	...
}

Now as it’s written, set_date() has no safety built into it, it
assumes blindly that the end-user would not do something rash like
replace the dashes with slashes, or remove them altogether, etc. A
more robust implementation would split by anything that was not a
number, so using the regex /[^d]+/ as the split point. Then it would
make sure that there were three elements in the resulting array before
proceeding, and would return a default date in ISO format if none of
that was true. But I leave that as an exercise (hopefully not an
exercise in frustration).

Walter

On Aug 22, 2009, at 5:08 PM, Todd wrote:

OK, I’ll forget about the fancy formatting. But what about simply
changing the default to,

mm-dd-YYYY

Is that still potentially problematic?

Todd


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Thanks for this, I’ll experiment with it. For the time being I think
I’ll leave the date pre-filled using

$mar->date = MyActiveRecord::DbDate();

It’s just easier.

I also tried a js calendar which was quite nice but I couldn’t get it
to pass the date variable (formatted as Y-m-d) to the db. On the back-
end all I get is 0000-00-00. The whole translating between formats
just wasn’t working for me. If the calendar just allowed me to use the
default MySQL format it would be much easier.

Todd


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options