How do I let the users enter 12hour format data entry (4:30pm) but have it save to a mySql field that is time format? Or is there such thing as a time picker?
Having some trouble getting it to display after I select a time and click save it displays all zeros (which is what is saved to the database). But when I click in the fields I see the value that was last selected in the date/time picker.I see the same value if I click in all the rows
Have you set a format for the date time picker, as noted in its documentation? For example you might use format: 'LT' in the opts object for the field. You would also need a deformatter on the server-side to confirm to an SQL time (assuming you are not just storing it as a text string). See the formatters documentation.
I am having trouble figuring out what syntax to use.
The field in mySQL is datetime. I have tried various things and get an error. This one doesn't get me an error but still not working. I would like for the date/time to show up as mm/dd/yyyy 3:30pm and write to mySQL in correct datetime format.
Field::inst( 'Appointments.AppointmentDate' )
//->validator( 'Validate::dateFormat', array(
// "format" => Format::DATE_ISO_8601,
// "message" => "Please enter a date in the format yyyy-mm-dd"
// ) )
->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 )
I would suggest first of all commenting the validator back in and using the appropriate date format for what you expect to be submitted.
The next thing to do would be to check what format is actually being submitted. You can use the developer tools in your browser to inspect the Ajax request and see what that is.
So then I changed a few things. First in mySQL I changed the field to be text instead of datetime. It still was sending a null. Second, with the field still being text format, I changed commented out the get/set formatter lines in the Editor. Then it saved. Then, leaving the two lines commented out I changed the field back to datetime format. After doing this it was returning all zeros, here is the Ajax being returned:
Now when it did save (text field with no get/set formatters) it saves as '07/14/2015 9:27 PM'
That is making me think I need to change the timepicker plugin. I want to keep it so the user still sees the m/d/yyyy hh:mm am/pm' but I'm guessing I need it to save as a format mysql likes for datetime.
I currently have the datepicker straight from the 'minimum setup' format and have no idea how to change it.
$.fn.dataTable.Editor.fieldTypes.datetime = $.extend( true, {}, $.fn.dataTable.Editor.models.fieldType, {
"create": function ( conf ) {
var that = this;
conf._input = $(
'<div class="input-group date" id="'+conf.id+'">'+
'<input type="text" class="form-control" />'+
'<span class="input-group-addon"><span class="glyphicon glyphicon-calendar"></span>'+
'</span>'+
'</div>'
)
.attr( $.extend( {}, conf.attr ) )
.datetimepicker( $.extend( {}, conf.opts ) );
return conf._input[0];
},
"get": function ( conf ) {
return conf._input.children('input').val();
},
"set": function ( conf, val ) {
conf._input.data("DateTimePicker").setDate( val );
},
// Non-standard Editor methods - custom to this plug-in. Return the jquery
// object for the datetimepicker instance so methods can be called directly
inst: function ( conf ) {
return conf._input.data("DateTimePicker");
}
} );
Answers
Ok, I found: https://editor.datatables.net/plug-ins/field-type/editor.datetimepicker-2
Having some trouble getting it to display after I select a time and click save it displays all zeros (which is what is saved to the database). But when I click in the fields I see the value that was last selected in the date/time picker.I see the same value if I click in all the rows
code:
Have you set a format for the date time picker, as noted in its documentation? For example you might use
format: 'LT'in theoptsobject for the field. You would also need a deformatter on the server-side to confirm to an SQL time (assuming you are not just storing it as a text string). See the formatters documentation.Allan
I am having trouble figuring out what syntax to use.
The field in mySQL is datetime. I have tried various things and get an error. This one doesn't get me an error but still not working. I would like for the date/time to show up as mm/dd/yyyy 3:30pm and write to mySQL in correct datetime format.
Have you tried it with the date format that you want?
If you get an error with that, what is the error?
Allan
I get the following error:
SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'AppointmentDate' cannot be null
I would suggest first of all commenting the validator back in and using the appropriate date format for what you expect to be submitted.
The next thing to do would be to check what format is actually being submitted. You can use the developer tools in your browser to inspect the Ajax request and see what that is.
Allan
Here is the Ajax request:
So then I changed a few things. First in mySQL I changed the field to be text instead of datetime. It still was sending a null. Second, with the field still being text format, I changed commented out the get/set formatter lines in the Editor. Then it saved. Then, leaving the two lines commented out I changed the field back to datetime format. After doing this it was returning all zeros, here is the Ajax being returned:
Note: when it did save (text field with no get/set formatters) it saves as '07/14/2015 9:27 PM'
Now when it did save (text field with no get/set formatters) it saves as '07/14/2015 9:27 PM'
That is making me think I need to change the timepicker plugin. I want to keep it so the user still sees the m/d/yyyy hh:mm am/pm' but I'm guessing I need it to save as a format mysql likes for datetime.
I currently have the datepicker straight from the 'minimum setup' format and have no idea how to change it.
That looks like the Ajax return. Can you show me the data submitted to the server - you might have to click on a "Header" tab.
Thanks,
Allan