Re: [Haskelldb-users] Problem using CalendarTimeT column

[Colin P. A. <co...@co...>]

>>>>> "Justin" == Justin Bailey <jgb...@gm...> writes:

    Justin> If you ever need to look at the actual SQL generated, you
    Justin> can use showQuery.

Well, I tried that, as I have finally reached the point where I need
those timestamps, and I'm again getting the error:

Server error: Convertible: error converting source data SqlString
"2009-09-29 19:31:12" of type SqlValue to type
Data.Time.LocalTime.LocalTime.ZonedTime: Cannot parse using default
format string "%Y-%m-%d %T%Q %z"


But showQuery gives me:

Project [("thumbnail",AttrExpr "thumbnail1"),("uploadTime",AttrExpr
"uploadTime1")] (Special (Top 1) (Special (Order [OrderExpr OpDesc
(AttrExpr "uploadTime1")]) (Project [("indexNumber1",AttrExpr
"indexNumber"),("caption1",AttrExpr "caption"),("thumbnail1",AttrExpr
"thumbnail"),("preview1",AttrExpr "preview"),("original1",AttrExpr
"original"),("uploadTime1",AttrExpr "uploadTime")] (Restrict (BinExpr
OpIn (AttrExpr "indexNumber") (ListExpr [ConstExpr (IntegerLit 6)]))
(BaseTable "imageTable"
["indexNumber","caption","thumbnail","preview","original","uploadTime"]))))) 
 
which isn't SQL.

I think 

showSql sounds better - so I shall try it - and indeed it gives me:

SELECT thumbnail1 as thumbnail,
       uploadTime1 as uploadTime
FROM (SELECT indexNumber as indexNumber1,
             caption as caption1,
             thumbnail as thumbnail1,
             preview as preview1,
             original as original1,
             uploadTime as uploadTime1
      FROM imageTable as T1
      WHERE indexNumber IN (6)) as T1
ORDER BY uploadTime1 DESC
LIMIT 1

This tells me that my constructed query is correct, but not what type
it is going to get uploadTime as.

Incidentally, if I just go into sqlite3 and make a query, it is
obvious to me that the stored value is a local time (we are currently
on UTC + 1 hour), without a time zone, which is fairly useless to me.
What I want to do is to store UTC (it's the only way that ORDER BY
clause is going to work correctly, given the possibility of
international uploads). It seems I can do that by changing
toCalendarTime to toUTCTime, but will the database know the
difference? 

I just tried it. Now the time is UTC time OK. But the column
definition is unchanged, of course.

So I need to be able to say that the query should interpret the time
as UTC. But how can I do that? 
-- 
Colin Adams
Preston Lancashire