No Results
How to Convert Timestamp String to Date
Article Contents

The easiest way to convert a timestamp string to a date format is to use the Filter Transform.

Example

We have uploaded the following JSON file containing different date formats:

{
    "data": {
        "iso-datetime": "2021-03-05 14:30:10",
        "iso-date": "2021-03-05",
        "euro-datetime": "05/03/21 14:30:10",
        "euro-date": "05/03/21",
        "us-datetime": "03/05/21 14:30:10",
        "us-date": "03/05/21",
        "iso-T-datetime-tz": "2021-03-05T14:30:10.123-0500",
        "iso-T-datetime": "2021-03-05T14:30:10.123",
        "custom": "03/05/21 02:30:10pm",
        "epochMs": 1614972610000
    }
}

In the JSON feed preview, those dates will be interpreted as strings or numbers:

To convert these dates to a date format, we are going to create a Filter Transform off of the JSON feed. Attributes need to have a valid DATE type after the filter transform so that any sorting or filtering function in the visualization can interpret the date value correctly.

On the Attributes tab, all dates from the JSON file are listed. In the Data Type column, we have selected Date from the dropdown.

For each attribute/date, we need to access the Date Parser by clicking the pencil icon next to Date in order to set the date format.

For our first attribute 03/05/21 02:30:10pm, we have set the Time Zone to New York, and for Date Format, we have selected Custom so that we could input the following format: MM/dd/yy hh:mm:ssa

For the attribute 1614972610000, we have set Date Format to Epoch Time in ms

For the attribute 05/03/21, we have set the Time Zone to New York, and for Date Format, we have selected dd/MM/yy

For the attribute 05/03/21 14:30:10, we have set the Time Zone to New York, and for Date Format, we have selected dd/MM/yy HH:mm:ss

For the attribute 2021-03-05, we have set the Time Zone to New York, and for Date Format, we have selected yyyy-MM-dd

For the attribute 2021-03-05 14:30:10, we have set the Time Zone to New York, and for Date Format, we have selected yyyy-MM-dd HH:mm:ss

For the attribute 2021-03-05T14:30:10.123, we have set the Time Zone to New York, and for Date Format, we have selected Custom so that we could input the following format: yyyy-MM-dd’T’HH:mm:ss.SSS

For the attribute 2021-03-05T14:30:10.123-0500, we have set the Time Zone to New York, and for Date Format, we have selected yyyy-MM-dd’T’HH:mm:ss.SSSX

For the attribute 03/05/21, we have set the Time Zone to New York, and for Date Format, we have selected MM/dd/yy

For the attribute 03/05/21 14:30:10, we have set the Time Zone to New York, and for Date Format, we have selected MM/dd/yy HH:mm:ss

In the Preview, all attributes/dates will be rendered as dates.

 

Parsing String into Date Using Moment.js

Some date formats cannot be parsed using the Filter transform date parser (e.g. 2022-08-12T10:00:00+02:00). In this case, we can use the Javascript transform and import the MomentJS library.

Example

We have created a server filesystem connection and then a CSV feed, where we have uploaded a file containing dates.
This is what our CSV feed looks like:

As seen in the screenshot, the date is interpreted as a string. To parse the string to a date, we are now going to create a JavaScript transform off of this CSV feed and then load the moment.js library.

Moment.js library is located in the tomcat folder of the edgeCore folder, and you will need to use the exact path to your edgeCore directory.
For example, this would be ours:

C:/Users/Admin/Downloads/edgeCore-4.3.11/tomcat/webapps/ROOT/WEB-INF/classes/nashorn/default-scripts/moment-with-locales.min.js

In the JavaScript transform, we are going to enter the following in the Script tab:

load('C:/Users/Admin/Downloads/edgeCore-4.3.11/tomcat/webapps/ROOT/WEB-INF/classes/nashorn/default-scripts/moment-with-locales.min.js');
// Please replace the path to edge install directory (C:/Users/Admin/Downloads) with your path

function getAttributes(sourceAttrs, nodeVars, secVars, sourceRecords) {

  var attributes = [];

  sourceAttrs.forEach(function(attrList) {

    attrList.forEach(function(attr) {

      attributes.push(attr);

    });

  });

  var sampleAttr = {

    name : 'TimestampDate', // Alphanumerics and underscores only; no leading, trailing, or adjacent underscores

    type : 'date', // 'string'|'int'|'long'|'number'|'boolean'|'date'

    isId : false, // true|false

    units : 'seconds', // 'millis'|'seconds' (if attribute is timestamp)

    format : null // string consisting of valid Java SimpleDateFormat pattern

  };

  attributes.push(sampleAttr);

  return jsAttributesSuccess(attributes, "Example Success");

  // can optionally return jsAttributesFailure(failureMsg) to handle failures

}

function getRecords(sourceRecords, nodeVars, secVars, attributes) {

  var records = [];

  sourceRecords.forEach(function(recordList) {

    recordList.forEach(function(record) {

      
    //logger.info('Date String: '+moment(record.dateNow, 'YYYY-MM-DDTHH:mm:ssZ').toDate().getTime());

      
      record.TimestampDate = moment(record.date, 'YYYY-MM-DDTHH:mm:ssZ').toDate().getTime()/1000;

      records.push(record);

    });

  });

  return jsRecordsSuccess(records, "Example Success");

  // can optionally return jsRecordsFailure(failureMsg) to handle failures

}

Note that in line record.TimestampDate = moment(record.date, ‘YYYY-MM-DDTHH:mm:ssZ’).toDate().getTime()/1000; you need to specify the column name of the column that contains the date in string format (in our example, that’s date as seen in the screenshot above).

Additionally, here you can also specify a custom format that will be used for parsing the date.

This is what the parsed date looks like in the preview:

Terms | Privacy
×