Time offset when merging in xlsx

Hi there.

I'm having trouble when merging dates from a MySQL database to a xlsx template.
Here is an example :
I have a date, say 01/03/2013 18:06:00 (french date format) in my database.
In the template, I have one field : [block.date] and another field [block.date;ope=tbs:date]
It should be the same, except that the second one is considered as a date and not a string in Excel.
But the result is that in the merged document, the first field contains "01/03/2013 18:06:00" (good but still a string) and the second contains "01/03/2013 17:06:00" (a real date, but not the good time). My time zone is Paris(GMT+1) and I suppose there something here.
Does someone have any idea ?

Thanks.

Hi Vaarteen;

In your database, what is the exact actual column type for this date ?

Hi Skrol. Thanks for answering.

It's a MySQL datetime type.

Hi,

I can easily reproduce your problem.
But I'm not really sure this is a bug.

Since your default time zone is GMT+1, then any date without time zone may be considered with the default time zone.
OpenTBS needs to convert date and time into PHP timestamps in order to calculate the XLSX timestamps. So it uses a PHP conversion when you use parameter "ope=tbs:date".

For now I can suggest a workaround: add the time zone in your SQL query, like this : "CONCAT(my_date, ' UTC') AS date". This will make OpenTBS to merge the date as you expect.

Thanks a lot.

I'll give it a try ASAP.
I guess the real problem is that Excel isn't aware of time zone, or at least doesn't know what to do with it. AFAIK, there's no Excel function to deal with time zone...