I've downloaded some data from a Synapse table. I chose the option to create this file as a CSV. The date fields do not appear in a date format, but as a numerical representation of the date value.
Is anyone aware of a way to convert this value back into a date format?
Created by Irwin Walters iwalters Ah, I see. Thank you very much. Hi Irwin:
Just for clarity, are you looking at the data in the table: syn551144?
This timestamp fields in the json files is time in seconds since an arbitrary start time (in practice it is the time in seconds since the phone was restarted). Since you are interested in the change throughout time I suggest using he first timestamp as t0 or the startime then compute the time as ti-t0 for each timestamp.
Thanks! I'm going to loop @BrianMBot and @larssono into this conversation - they are more knowledgeable about this particular data than I! I am looking at the Walking Activity table. The column which has the attached file in json format is Accel_Walking_Outbound. Could you point me to which table you're working with so I can inspect it further? Thanks! Hello again,
so the numerical date formats stored within an attached file are different then the numerical date formats stored in a table object.
Using the convert from Epoch to Windows date functions correctly changes the dates value stored in the table, but not the date values stored in the attached files.
How do I convert the numerical representation of date stored in an attached file to a readable date (example: yyyy-mm-dd hh:mm:ss)
eg:
Attached File: 19890505.123456
Table: 5555555 GREAT!
Thank you very much Kenneth Our timestamps are the number of milliseconds since the [Unix epoch](http://www.epochconverter.com/), which is `1970-01-01`. In general timestamps are the number of **seconds** since the epoch, not milliseconds, so your range error is probably due to this. Divide the timestamp by 1000 and should be good.
Different languages and programs (like Excel) have different standards for converting relative to a specific date (like the Unix Epoch). Here's how you would do this with base `R`, using Pacific Standard Time as the time zone:
```
library(synapseClient)
synapseLogin()
# This table has a column of dates named `MyDate`
tbl <- synTableQuery('SELECT * FROM syn3988772')
d <- tbl@values
as.POSIXct(d$MyDate/1000, tz="America/Los_Angeles", origin='1970-01-01')
```
This outputs:
```
[1] "2014-12-31 16:00:00 PST" "2013-12-31 16:00:00 PST" "2013-02-01 16:00:00 PST"
```
I wrote a simple application using C# to convert the date columns in the spreadsheet, but the numerical representation of the date in the spreadsheet apparently falls out of a valid date range.
Thank you very much for your help. I will look into using R. These are stored as timestamps. If you are working in R, check out the `lubridate` [package](https://cran.r-project.org/web/packages/lubridate/index.html) for easy manipulation - it can be done with base `R` functions as well, I always found them confusing. I opened an [issue](https://github.com/Sage-Bionetworks/synapseDocs/issues/201) to update our [tables documentation](http://docs.synapse.org/articles/tables.html) with more information about dates and timestamps.
Drop files to upload
Dates in downloaded CSV file appear as a number page is loading…