Date format between custom field and database field

Support MB Custom Table Date format between custom field and database fieldResolved

Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
    Posts
  • #13847
    proyectohappyweb@gmail.com[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

    #13866
    Anh TranAnh 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.
    #13871
    proyectohappyweb@gmail.com[email protected]
    Participant

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

    #13879
    Anh TranAnh 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.

    #13885
    proyectohappyweb@gmail.com[email protected]
    Participant

    Perfect!! Many thanks!!

Viewing 5 posts - 1 through 5 (of 5 total)
  • You must be logged in to reply to this topic.