Support › MB Custom Table › Date format between custom field and database fieldResolved
I created my custom table
MB_Custom_Table_API::create( 'articulos', array(
'fecha_inicio_evento' => 'DATE',
And the custom field:
array ( 'id' => 'fecha_inicio_evento', 'type' => 'date', 'name' => 'Fecha Inicio', 'js_options' => array( 'dateFormat' => "dd-mm-yy", ), 'columns' => 6, ),
In the front end it looks well, you can select the date and see 25-10-2018 for example, but in the database is stored like this 0000-00-00.
I see that this is the format of mysql date
But how can I do to save the date well?
If I change the format in the database:
'fecha_inicio_evento' => 'VARCHAR (64)',
The date is saved with the frontend format, but don't know if is the best way to save a date in a varchar field.
This is a great question! While saving the date with
varchar is not a problem, I think saving in
date format of MySQL might be a better idea. However, as you see, MySQL forces data saved in the DB in its format, not what you enter. So, I think there are 2 solutions for this:
varchar, so you save what you enter
save_formatattribute for the field, set it to the format of MySQL and you're free to go. It tells Meta Box to store value in one format and display in another format.
ok perfect, I will use option 2:
array ( 'id' => 'fecha_inicio_evento', 'type' => 'date', 'name' => 'Fecha Inicio', 'js_options' => array( 'dateFormat' => "dd/mm/yy", ), 'save_format' => 'Y-m-d', 'timestamp' => false, ),
Also, in docs: https://docs.metabox.io/fields/date/
"Saving in timestamp also allows you to query posts with a specific order by this field".
I try to put timestamp to true. I tried to change the field of database from 'fecha_inicio_evento' => 'DATE', to 'fecha_inicio_evento' => 'TIMESTAMP', and I can not make it work...
I docs it says "However, you still can sort posts by meta value if you set date format to something similar to yy-mm-dd.".
With my settings, could I sort by date?
According to MySQL docs:
The TIMESTAMP syntax produces a DATETIME value in MySQL because DATETIME has a range that more closely corresponds to the standard SQL TIMESTAMP type, which has a year range from 0001 to 9999.
TIMESTAMP has the exact format as
DATETIME, which is
Y-m-d H:i:s. It's not the same as the Unix timestamp as we use in PHP.
And saving date with format
Y-m-d still makes it sortable. So, it's okay to set the column type to
DATETIME and set the
Perfect!! Many thanks!!