DATE format GTM in template

Dashboard
1

Hello

I have a dashboard with template node

Capture d'écran 2023-12-28 141734
Capture d'écran 2023-12-28 141734718×582 19.9 KB

But the date in my database is on format YYYY-MM-DD, after select on Mysql the value is in format
Capture d'écran 2023-12-28 142009

What is the solution for any value il in my template in format YYYY-MM-DD not GTM

2

Since you are French-speaking, don't you want this kind of format?
"20/12/2012 04:00:00"

Take a look here, I don't know if you can use it directly in the template or if you have to modify the message before

3 
    <td><center>{{msg.payload[$index].date.toLocaleString('fr-FR', { timeZone: 'UTC' })}}</center></td>

I changed it like that but it doesn't work

4

I would think you can adjust your sql statement to get the correct format.

5

this is my request mysql

SELECT * FROM authorized ORDER BY name ASC, lastname ASC
6

Would need to know column types, to make a reply that would work. But try naming the columns instead of *, and also you can use sql functions on the columns then. Dont for get to use AS name function to make the returned object properties more readable.

e.g

SELECT DATE_FORMAT(date_column, "%Y") AS date....

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

7

Try with

{{new Date(msg.payload[$index].date).toLocaleString('fr-FR', { timeZone: 'UTC' })}}
1 Like
8

no i have no entry in my table with this format

9

this is my database

Capture d'écran 2023-12-28 144457
Capture d'écran 2023-12-28 144457855×133 88.6 KB

10

Modify the format in msg.payload before you pass it to the template node.

11

Try

SELECT 
  ID,
  name,
  lastname,
  plate,
  DATE_FORMAT(date, "%Y-%m-%d") AS date 
FROM 
  authorized 
ORDER BY 
  name ASC, 
  lastname ASC;

Or similar

12

this is good but the code is

msg.topic = "SELECT ID ,name, lastname,plate, DATE_FORMAT(date, '%Y-%m-%d') AS date FROM authorized ORDER BY name ASC, lastname ASC"; return msg;

13

If you want the dates to show in your local time,
you'll have to use an additional CONVERT_TZ mysql date function
in order to first convert it to your timezone and then format it ..
otherwise the dates would sometimes show incorrectly.

so adding to @E1cid 's solution

SELECT ID, name, lastname, plate, DATE_FORMAT(CONVERT_TZ(date,'+00:00','+10:00'), '%Y-%m-%d') AS date FROM authorized ORDER BY name ASC, lastname ASC
  • +10:00 as an example. adjust to your local tz

CONVERT_TZ() function

1 Like
14

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.