Date format between custom field and database field
Support › MB Custom Table › Date format between custom field and database fieldResolved
- This topic has 4 replies, 2 voices, and was last updated 6 years, 1 month ago by
[email protected].
-
AuthorPosts
-
March 21, 2019 at 11:56 PM #13847
[email protected]
ParticipantHEllo,
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
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.htmlBut 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.Thanks,
SergioMarch 22, 2019 at 5:39 PM #13866Anh Tran
KeymasterHi Sergio,
This is a great question! While saving the date with
varchar
is not a problem, I think saving indate
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:- Just use
varchar
, so you save what you enter - Use the
save_format
attribute 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.
March 22, 2019 at 10:39 PM #13871[email protected]
Participantok 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/
Say that:
"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?
Many thanks.
March 23, 2019 at 9:14 PM #13879Anh Tran
KeymasterAccording 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.
That means
TIMESTAMP
has the exact format asDATETIME
, which isY-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 toDATETIME
and set thesave_format
asY-m-d
.March 23, 2019 at 10:27 PM #13885[email protected]
ParticipantPerfect!! Many thanks!!
- Just use
-
AuthorPosts
- You must be logged in to reply to this topic.