Migx database schema and date and time processing

Support Extras
1

Hi All,

I am facing a problem with Migx where I have on my MySQL Schema some dates with datetime format, like this:

<field key="start_date" dbtype="datetime" phptype="datetime" null="true" />
<field key="end_date" dbtype="datetime" phptype="datetime" null="true" />

And if I don’t set them, the value shown for 0000-00-00 00:00:00 will be 1970-01-01 00:00:00.
This is specially tricky because on the database it is added 1970-01-01 00:00:00 and filtering all this it is a bit of an headache.

I’m wondering if using unix time format would solve this problem or if you were able to sort this out with other ways? I am on the critical phase of defining now these settings. Later will be too late :frowning:

Thanks in advance for your help.

2

What do you want the value to be if you don’t set it?

3

Hi Mark,

Should be 0000-00-00 00:00:00.
One of the thinks I thought it could work was to have this field in the database as varchar instead of date. The empty value would be stored as empty or as 0000-00-00 00:00:00 but I know it is not the best solution.

4

Try:

<field key="start_date" dbtype="datetime" phptype="datetime" null="true" default="0000-00-00 00:00:00" />

Note that MySQL Strict mode doesn’t support the zero value for a datetime field.

It’s also worth checking what your current model does. Does it actually write 1970-01-01 00:00:00 to the database, or is a literal NULL value stored and that is only rendered as such? In the latter case, it may be something you can tweak in the rendering, but I don’t know much about MIGX.

I do personally tend to use unix timestamps because they’re less… magical.

5

Well, when trying to change these fields, this is what I get:

    [2019-08-19 13:24:13] (ERROR in xPDOManager_mysql::alterField @ D:\xampp\htdocs\core\xpdo\om\mysql\xpdomanager.class.php : 363) Error altering field InscricoesCartasDates->end_date: Array
    (
        [0] => 42000
        [1] => 1067
        [2] => Invalid default value for 'start_date'
    )

By checking the database for saved values, it is saves as 1970-01-01 if done by Migx but on the DB stays null.
Here is the field definition in Migx:

{
          "MIGX_id":11916,
          "field":"start_date",
          "caption":"Data de inicio",
          "description":"(Formato: AAAA-MM-DD)",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"date",
          "validation":"",
          "configs":{
            "allowBlank":true,
            "disabledDates":"0000-00-00",
            "disabledDays":"",
            "minDateValue":"",
            "minTimeValue":"",
            "maxDateValue":"",
            "maxTimeValue":"",
            "startDay":"",
            "timeIncrement":"",
            "hideTime":true
          },
          "restrictive_condition":"",
          "display":"none",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"none",
          "useDefaultIfEmpty":"0",
          "pos":6
        }

I am not really sure what more I can do.
Thanks in advance for your time.

6

I recognized that date, its apparently a default value (that converts to 0, it seems).

Ok I just googled, here is a thread about MIGX, with a workaround by the OP

Here also is more info, the case is different but this is where someone says that date converts to zero, and some of the workaround might be useful for you

I think, if you need dates, you will want to save them as part of your processing. I think this date is appearing because there is not a date saved for that item, but I could be wrong about that.

Searching Modx “1970-01-01” also has more hits

Edit: This looks like a solution from BobRay on the first page, to fix the date of tv creation for every tv.

https://forums.modx.com/thread/80199/set-default-value-for-date-tv-to-current-date

1 Like
7

Thanks @nuan88, let me try to explore these.
I will share the outcome here.

Cheers and many thanks

8

I think I speak for everyone in the community when I say, we are always here :slight_smile:

2 Likes