Date Saved in String/varchar and it's still be presented as date

Wappler General Need Help
1

Have a day into this and it's not making sense.

Date is stored in a nodejs/mariadb varchar field: 2024-10-23 00:00:00
On localhost with a simple BS5 Generated Table the field displays: 2024-10-23T04:00:00.000Z

On the same page just displaying a single number from the same query: 2024-10-23 00:00:00

On my server it displays one day behind. I've read many of the Data/UTC topics and thought for my needs I thought I beaten the one day behind issue by changing the field type to varchar. But it didn't help.

Questions:

  1. How can the system look at a varchar field and reformat as a datetime?
  2. What's wrong with my assumption that the varchar type would alleviate the server backdating the date?
2

If you're still needing help...it always helps when you can post up code or screenshots showing what you've described.

  1. Some code or screenshots on how you're building your table may help the community troubleshoot with you.

  2. As for the one-day behind issue, this is probably because the time is set to 04:00:00.000Zulu (UTC). Depending on when you view the output in your local timezone, it may be causing the one-day behind issue. Have you tried converting to local time before display?

3

Thanks Graybeard - I was anticipating one of the old pros would have had a suggestion to my issue.

Here's my test page:

<!-- Wappler include head-page="layouts/main" fontawesome_5="cdn" is="dmx-app" id="wip" appConnect="local" bootstrap5="yeti" components="{dmxFormatter:{},dmxBootstrap5TableGenerator:{}}" -->
<meta name="ac:route" content="/wip">
<dmx-serverconnect id="serverconnect1" url="/api/admin/get_time"></dmx-serverconnect>
<div class="row">
    <div class="col">
        <table class="table">
            <thead>
                <tr>
                    <th>Request opening date</th>
                    <th>Request closing date</th>
                    <th>Request last update</th>
                </tr>
            </thead>
            <tbody is="dmx-repeat" dmx-generator="bs5table" dmx-bind:repeat="sc_req_page_list.data.repeat" id="tableRepeat1">
                <tr>
                    <td dmx-text="request_opening_date"></td>
                    <td dmx-text="request_closing_date"></td>
                    <td dmx-text="request_last_update"></td>
                </tr>
            </tbody>
        </table>
    </div>
</div>
<div class="row">
    <div class="col">
        <p>VAR:&nbsp; &nbsp;{{var1.datetime}}</p>
        <p>NOW:&nbsp; &nbsp;{{serverconnect1.data.server_time}}</p>
        <p>UTC:&nbsp; &nbsp;{{serverconnect1.data.server_time_utc}}</p>
        <p>Straight Req_id opening date:&nbsp; &nbsp;{{serverconnect1.data.query1[0].request_opening_date}}</p>
        <p>Viol:&nbsp; &nbsp;{{serverconnect1.data.query[0].violation_fine_start}}</p>
        <p>Viol ISO:&nbsp; &nbsp;{{serverconnect1.data.query[0].violation_fine_start.toISODate()}}</p>
        <p>Viol ISO:&nbsp; &nbsp;{{serverconnect1.data.query[0].violation_fine_start.toISODate().addDays(17).formatDate('MM/dd/yyyy')}}</p>
        <p>Fine:&nbsp; &nbsp;{{serverconnect1.data.query1[0].request_opening_date.addDays(17)}}</p>
    </div>
</div>

Here's the test query:

{
  "meta": {
    "$_GET": [
      {
        "type": "text",
        "name": "sort"
      },
      {
        "type": "text",
        "name": "dir"
      }
    ]
  },
  "exec": {
    "steps": [
      {
        "name": "server_time",
        "module": "core",
        "action": "setvalue",
        "options": {
          "value": "{{NOW}}"
        },
        "meta": [],
        "outputType": "text",
        "output": true
      },
      {
        "name": "server_time_utc",
        "module": "core",
        "action": "setvalue",
        "options": {
          "value": "{{NOW_UTC}}"
        },
        "meta": [],
        "outputType": "text",
        "output": true
      },
      {
        "name": "query",
        "module": "dbconnector",
        "action": "select",
        "options": {
          "connection": "hoa_member",
          "sql": {
            "type": "select",
            "columns": [
              {
                "table": "t_violation",
                "column": "violation_fine_start",
                "recid": 1
              }
            ],
            "params": [],
            "table": {
              "name": "t_violation"
            },
            "primary": "violation_id",
            "joins": [],
            "query": "select `violation_fine_start` from `t_violation`"
          }
        },
        "output": true,
        "meta": [
          {
            "type": "text",
            "name": "violation_fine_start"
          }
        ],
        "outputType": "array"
      },
      {
        "name": "query1",
        "module": "dbconnector",
        "action": "select",
        "options": {
          "connection": "hoa_member",
          "sql": {
            "type": "select",
            "columns": [
              {
                "table": "m_request",
                "column": "request_opening_date",
                "recid": 1
              }
            ],
            "params": [],
            "table": {
              "name": "m_request"
            },
            "primary": "request_id",
            "joins": [],
            "query": "select `request_opening_date` from `m_request`"
          }
        },
        "output": true,
        "meta": [
          {
            "type": "text",
            "name": "request_opening_date"
          }
        ],
        "outputType": "array"
      }
    ]
  }
}

Here's the date field in Wappler:

image
image948×1788 79.6 KB

Here's the table structure via Navicat:

image
image2147×919 48.1 KB

I changed the server connect time to match my local time to alleviate the one day difference, but that doesn't overcome my confusion as to why the varchar is displaying as a date.

Thanks in advance...

4

Still looking for insight to understand how Wappler reads a varchar as a date. Thanks

5

Very confusing,i agree.

Try adding the tostring() formatter

6

Thanks for the reply Brian - just comforting to find out my inexperience isn't the complete issue.

<td dmx-text="request_opening_date.toString()"></td>

returns an empty cell in the report.

7

Can you show the relevant server action output as seen in the Developers console?

8

the response:
image

The report:
image

9

Does it make any difference if you simply change:

<td dmx-text="request_opening_date"></td>

To:

<td>{{request_opening_date}}</td>
10

@Teodor - same result

12

One other note - it does allow date formatting..

13

Strings with a date in ISO format are detected as dates. This is due to limitation in the JSON format and several database drivers also return the dates as string and meta data about the column is often not available. That's why the dates are always converted to strings and converted back again to dates if the specific string format was detected.