Converting UNIX format to normal human format

General
1

Hello to All,

I dad issues when trying to display information from sqlite to a ui template as a table and one of the columns needed to be a readable timestamp. Because I saved the date with a timestamp inject or Date.now to the database it was in UNIX format (I needed 1 hour to find that the format is called UNIX format :slight_smile: ). I managed to transform the sqlite output data TIMESTAMP with a function node and the below code. The sql date column is called TIMESTAMP and I have many recoreds (around 100) and because of this I use msg.payload[i].TIMESTAMP to match it and change it before providing the msg.payload to the ui template. I used the example below and now with this code it works nice and hope this code helps someone. I first tried saving the date in the correct format with th DATE/TIME formater node in the sqlite but it did not accept this and after this I tried to add to the UI Template but it uses angular js and for some reason it did not work, so I think this is a nice option to change it with a func node and then provide the data to the ui template to create a html table with the values.

var i;
for (i = 0; i <msg.payload.length; i++) {

unix_time = msg.payload[i].TIMESTAMP;

var a = new Date(unix_time * 1000);
var months = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'];
var year = a.getFullYear();
var month = months[a.getMonth()];
var date = a.getDate();
var hour = a.getHours();
var min = a.getMinutes();
var sec = a.getSeconds();
msg.payload[i].TIMESTAMP = date + ' ' + month + ' ' + year + ' ' + hour + ':' + min + ':' + sec ;

}

return msg;

2

This all seems a little complex.

First thing to make sure is that all of your date/time values are in UTC so that you don't trip over daylight saving and timezone issues. Convert to local when displaying.

Second thing to note is that SQL timestamps are a modified version of ISO8601.

const dt8601 = '2021-04-01T12:34:56.789Z'
const dtSql = '2021-04-01 12:34:56'
const dt8601Js = new Date(dt8601)
const dtSqlJs = new Date(dtSql)

console.log('ISO8601: ', dt8601, '. SQL: ', dtSql)
console.log('new Date(ISO8601): ', dt8601Js, '. new Date(SQL): ', dtSqlJs)

const dt8601converted = dt8601.replace('T', ' ').slice(0,20)
console.log('ISO8601 string transformed to SQL: ', dt8601converted)

ISO8601:  2021-04-01T12:34:56.789Z . SQL:  2021-04-01 12:34:56 . 
new Date(ISO8601):  2021-04-01T12:34:56.789Z . new Date(SQL):  2021-04-01T11:34:56.000Z
ISO8601 string transformed to SQL:  2021-04-01 12:34:56.

You can convert between them using a simple text transform if you need to just watch out for how the SQL db handles local vs UTC.

Both of those string formats should be convertible to JavaScript timestamp as shown. Note that JavaScript tends to automatically convert a Date object back to ISO8601 when it has to serialise it (e.g. toString()). That's because ISO8601 is both human and machine readable and is fully unambiguous.

3

I found a better solution based on the post Timestamp to current date and time? - #22 by Paul-Reed .

var i;
for (i = 0; i <msg.payload.length; i++) {

var now = new Date(msg.payload[i].TIMESTAMP).toLocaleString("en-GB");

msg.payload[i].TIMESTAMP = now;

}

return msg;