Database

 
 
Picture of Kyle Scallan
Standard Database Module - Dates
 

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

 
Average of ratings: -
Picture of Helen Foster
Re: Standard Database Module - Dates
Group DevelopersGroup Documentation writersGroup Moodle HQGroup Particularly helpful MoodlersGroup Testers

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.

 
Average of ratings: -
Picture of Itamar Tzadok
Re: Standard Database Module - Dates
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

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 smile

 
Average of ratings:Useful (1)
Picture of Helen Foster
Re: Standard Database Module - Dates
Group DevelopersGroup Documentation writersGroup Moodle HQGroup Particularly helpful MoodlersGroup Testers

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.

 
Average of ratings: -
Picture of Itamar Tzadok
Re: Standard Database Module - Dates
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

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. smile

 
Average of ratings: -
Gordon McLeod
Re: Standard Database Module - Dates
 

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.

 
Average of ratings: -