Date format between custom field and database field

This topic has 4 replies, 2 voices, and was last updated 5 years, 9 months ago by [email protected].

Viewing 5 posts - 1 through 5 (of 5 total)

Author

Posts
March 21, 2019 at 11:56 PM #13847
[email protected]
Participant
HEllo,

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.html

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.

Thanks,
Sergio

Add New Note to this Reply
March 22, 2019 at 5:39 PM #13866
Anh Tran
Keymaster
Hi Sergio,

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:
1. Just use varchar, so you save what you enter
2. 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.
Add New Note to this Reply
March 22, 2019 at 10:39 PM #13871
[email protected]
Participant
ok perfect, I will use option 2:
```
        array (
            'id' =&gt; 'fecha_inicio_evento',
            'type' =&gt; 'date',
            'name' =&gt; 'Fecha Inicio',
            'js_options' =&gt; array(
                'dateFormat' =&gt; "dd/mm/yy",
            ),
            'save_format' =&gt; 'Y-m-d',
            'timestamp' =&gt; 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.

Add New Note to this Reply
March 23, 2019 at 9:14 PM #13879

Anh Tran
Keymaster

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.

That means 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 save_format as Y-m-d.

Add New Note to this Reply

March 23, 2019 at 10:27 PM #13885

[email protected]
Participant

Perfect!! Many thanks!!

Add New Note to this Reply
Author

Posts

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic.