timediff results imported via ODBC ver. 3.51.27 - feature or issue?

--0016e647121a907e9b0492187eb4
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hi list,

I am trying to import into MS Excel 2003 results from timediff calculation.
I am using MS Query to define query to retrieve my data and in MS Query
window I get correct results - I mean time in hh:mm:ss format, but after
switching to Excel column with results is changed. Excel adds current date
to data queried from database. Example:

in MS Query:

2010-02-05 05:32:51 | 2010-02-08 07:31:33 | 73:58:42

In Excel

2010-02-05 05:32 2010-02-08 07:31 10-paź-11 01:58:42 I tried to look u=
p is
it a feature or a bug, but after hours I am still no sure how to treat this=
..
What are your opinions on this problem?

rgds Adam

--0016e647121a907e9b0492187eb4--

RE: timediff results imported via ODBC ver. 3.51.27 - feature or issue?

>-----Original Message-----
>From: adas71 Gazeta.pl [mailto:adas71@gazeta.pl]
>Sent: Friday, October 08, 2010 6:30 AM
>To: myodbc@lists.mysql.com
>Subject: timediff results imported via ODBC ver. 3.51.27 - feature or issue?
>
>Hi list,
>
>I am trying to import into MS Excel 2003 results from timediff calculation.
>I am using MS Query to define query to retrieve my data and in MS Query
>window I get correct results - I mean time in hh:mm:ss format, but after
>switching to Excel column with results is changed. Excel adds current date
>to data queried from database. Example:
>
>in MS Query:
>
>2010-02-05 05:32:51 | 2010-02-08 07:31:33 | 73:58:42
>
>In Excel
>
>2010-02-05 05:32 2010-02-08 07:31 10-paz-11 01:58:42 I tried to look up is
>it a feature or a bug, but after hours I am still no sure how to treat this.
>What are your opinions on this problem?
>
[JS] It is a fundamental design in Excel. Excel stores dates as floating-point
numbers. The only way to make them dates is by formatting them as dates. What
is happening to you is that Excel is being told by ODBC that it is getting
date/time data for that field.

Try using

CAST(TIMEDIFF('2010-02-08 07:31:33', '2010-02-05 05:32:51') AS BINARY

and see what you get.

If you need to do this on the Excel side, try formatting the cells with a
custom format:

[hh]:mm:ss

If that doesn't work, you'll need to perform a calculation in a separate
column:

=INT(A1*24) & ":" & INT(MINUTE(A1)) & ":" & SECOND(A1)

where A1 holds the results of your query. This should work regardless of your
local date format, **I THINK**. I couldn't

>rgds Adam




--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

Re: timediff results imported via ODBC ver. 3.51.27 - feature or issue?

--90e6ba6e8dd01a584e0492575b42
Content-Type: text/plain; charset=UTF-8

Hello Jerry,

I know how MS Excel handles dates etc. I am tring to keep data calculations
on mySQL side as I will benefit from this when I will do pivotable reports
in Excel - I will have an auto named data for my report.

I tried to modify my query using CAST, but this function is not working in
database. BTW, I don't know why. Knowing that, I used

CONVERSE (TIMEDIFF('2010-02-08 07:31:33', '2010-02-05 05:32:51'), binary)

Again, data in MS Query window is correct (only hh:mm:ss part), but after
returning to Excel column with timediff calculation is not present.

I also tested what if I convert result into CHAR type - Excel is displaying
my calculation column and data in this column is correct, but it looks like
Excel is fooled by ODBC to treat data as character strings. Just entering
any cell and confirming with ENTER key changes data into "real" time value
that can be used by Excel for calculations.

Regards

Adam

2010/10/8 Jerry Schwartz

> >-----Original Message-----
> >From: adas71 Gazeta.pl [mailto:adas71@gazeta.pl]
> >Sent: Friday, October 08, 2010 6:30 AM
> >To: myodbc@lists.mysql.com
> >Subject: timediff results imported via ODBC ver. 3.51.27 - feature or
> issue?
> >
> >Hi list,
> >
> >I am trying to import into MS Excel 2003 results from timediff
> calculation.
> >I am using MS Query to define query to retrieve my data and in MS Query
> >window I get correct results - I mean time in hh:mm:ss format, but after
> >switching to Excel column with results is changed. Excel adds current date
> >to data queried from database. Example:
> >
> >in MS Query:
> >
> >2010-02-05 05:32:51 | 2010-02-08 07:31:33 | 73:58:42
> >
> >In Excel
> >
> >2010-02-05 05:32 2010-02-08 07:31 10-paz-11 01:58:42 I tried to look up is
> >it a feature or a bug, but after hours I am still no sure how to treat
> this.
> >What are your opinions on this problem?
> >
> [JS] It is a fundamental design in Excel. Excel stores dates as
> floating-point
> numbers. The only way to make them dates is by formatting them as dates.
> What
> is happening to you is that Excel is being told by ODBC that it is getting
> date/time data for that field.
>
> Try using
>
> CAST(TIMEDIFF('2010-02-08 07:31:33', '2010-02-05 05:32:51') AS BINARY
>
> and see what you get.
>
> If you need to do this on the Excel side, try formatting the cells with a
> custom format:
>
> [hh]:mm:ss
>
> If that doesn't work, you'll need to perform a calculation in a separate
> column:
>
> =INT(A1*24) & ":" & INT(MINUTE(A1)) & ":" & SECOND(A1)
>
> where A1 holds the results of your query. This should work regardless of
> your
> local date format, **I THINK**. I couldn't
>
> >rgds Adam
>
>
>
>

--90e6ba6e8dd01a584e0492575b42--