Need help with a couple of issues relative to the "dates" field in the standard database module.
1. When exporting into csv, the date field is a series of numbers (some type of code) and I am unable to format it to read as the actual date.
2. I need to upload several entries via csv. I am unable to get the date field correct. It always defaults to 1 January 1970.
I'm sure it's a simple fix or setting. Any help would be greatly appreciated.
Thanks,
Kyle Scallan
Hi Kyle,
It sounds as though the dates are exported as Unix timestamps. If you google 'timestamp converter', you'll find websites with handy conversion tools.
With ods you can use the following formula:
=(A1/86400+DATEVALUE("1970-01-01"))
where A1 stands for the cell with the unix timestamp. For example:
| 1349857600 | 2012-10-10 |
In the other direction:
=(A1-DATEVALUE("1970-01-01"))*86400
| 1999-02-08 | 918432000 |
With Excel its DATE instead of DATEVALUE.
hth
Thanks Itamar for the formula for converting Unix timestamps.
I just came across a tracker issue, MDL-32637, requesting dates to be converted on export, if anyone wishes to vote for this improvement to be implemented.
The Dataform can export dates in almost every format. It also imports dates from date strings. So anyone who wishes to attempt the improvement in the Database module is welcome to take some ques from the Dataform code.
Voted!
I just encountered the issue this morning - having human readable dates exported from database activity is a must have for using database effectively. I've seen the workaround conversion that can be run on a spreadsheet - not difficult, but a step beyond any staff who aren't computer confident.
Regards, Gordon.
