DATE from TIMESTAMP

[ketanaga]

Hi 

I am converting timestamp to datetime format with basetype DATETIME with the code "var date = new Date(Timestamp * 1000)". The result of it is being saved in an datafield of DATETIME basetype. I want to extract the date part of the result since i want to apply aggregate function on it. To extract the date part of the column, I am using (getFullYear(),getMonth(), getDay()) methods. But it gives me an error "Cant find getFullYear() function in "Wed Jan 15 2020 06:26:30 GMT+0530 (IST) "" Even though my DATETIME is being stored in the format YYYY/MM/DD HH:MM:SS why is it pointing to the GMT format to extract the DATE object

A way aorund it would highly be appreciated

regards

[rjanardan]

Woks fine for me using, for example: 

me.date1 = new Date(1579069590 * 1000);
var result = me.date1.getFullYear();

Could you please share your full code? Also, what is the ThingWorx version that you are using?

[ketanaga]

for(i = 0; i<result.getRowCount(); i++){
    var date = new Date(result.rows[i].Timestamp * 1000);
    var year = date.getFullYear();
    var month = date.getMonth();
    var day = date.getDay();
    result.rows[i].Timestamp = year+'-'+month+'-'+day;

This is the code I am using I am getting the timestamp from csv file which I am reading and converting it into DATETIME in the same infotable under Timestamp datafield.

I am using 8.3.3 version

[rjanardan]

What is the data type of the Timestamp field of the datashape on which the result infotable is based upon?

I am still unable to replicate it. If you just want a string representation of your date then you could use below code(dateValue is of type DATETIME): 

var result = dateFormatISO(dateValue).substr(0,10);

[ketanaga]

Hi

Datatype of the Timestamp field is DATETIME so when I upload my CSV the EPOCH Timestamp automatically converts into DATETIME format but i multiplied the result with 1000 to get the present date of timestamp.

If i get a string format of the DATETIME will I be able to feed it in the Time-series chart?

[rjanardan]

In this line, you seem to be assigning a string to Timestamp

    result.rows[i].Timestamp = year+'-'+month+'-'+day;

[ketanaga]

Hi

So what will be the exact solution for the problem?

[rjanardan]

If you want to remove the time part of the datetime property while displaying it in a time series chart, you can do this by specifying the appropriate XAxisFormat.

As far as I know, DATETIME will always have a time component. 

As per this article, aggregate functions cannot be applied to dates in ThingWorx. 

Also, I noticed that you are using getDay() which will give you the day of the week and not the date(getDate()).