haskelldb-users — For questions, suggestions, bug reports and patches

Re: [Haskelldb-users] Problem using CalendarTimeT column

[Justin B. <jgb...@gm...>]

On Wed, Sep 30, 2009 at 12:47 AM, Colin Paul Adams
<co...@co...> wrote:
> The reason I say this, is because I tried switching from sqlite3 to
> postgresql. It doesn't work there either. But this time, instead of
> the column being of type "timestamp", it is of type "timestamp without
> time zone", which is clearly wrong, as CalendarTime includes timezone
> information.
>

I looked into this yesterday and found pretty much the same thing. The
HaskellDB driver interface for database connections is too ridig and
doesn't allow you to specify the type you want for a DateTime column,
which in turn forces the driver implementor to decide on a parsing
strategy.

It's an unfortunate situation, but I'm glad you found a workaround. If
you feel like digging into the library to improve the API I'd be glad
to help.

Justin

Re: [Haskelldb-users] Problem using CalendarTimeT column

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

>>>>> "Colin" == Colin Paul Adams <co...@co...> writes:

    Colin> I can probably bodge this with an SQL ALTER TABLE ... ALTER
    Colin> COLUMN .. statement to make this "timestamp with time
    Colin> zone", but I think haskelldb should be generating the
    Colin> correct types anyway (for sqlite3 this may not be
    Colin> possible).

I can confirm that this bodge makes the problem disappear.
-- 
Colin Adams
Preston Lancashire

Re: [Haskelldb-users] Problem using CalendarTimeT column

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

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

    Justin> As far as I can tell, this is an issue with HDBC's
    Justin> Convertible class/package more than haskelldb. The
    Justin> instance of Convertible for the time type
    Justin> (Data.Time.LocalTime.LocalTime.ZonedTime) is apparently
    Justin> trying to use the format string "%Y-%m-%d %T%Q %z" to
    Justin> parse the data.  Notice %z - that is a time zone
    Justin> specifier, and it does not show up in the example value
    Justin> ("2009-09-28 11:48:37").

    Justin> If you can manage to get your own instance of Convertible
    Justin> defined, you should be able to define your own format
    Justin> string to parse the data.  Alternatively, if you can get
    Justin> SqlLite to include timezone information you can probably
    Justin> get the value to parse.

I think it's a problem with the way haskelldb defines columns for
CalendarTimeT.

The reason I say this, is because I tried switching from sqlite3 to
postgresql. It doesn't work there either. But this time, instead of
the column being of type "timestamp", it is of type "timestamp without
time zone", which is clearly wrong, as CalendarTime includes timezone
information.

I can probably bodge this with an SQL ALTER TABLE ... ALTER COLUMN
.. statement to make this "timestamp with time zone", but I think
haskelldb should be generating the correct types anyway (for sqlite3
this may not be possible).
-- 
Colin Adams
Preston Lancashire

Re: [Haskelldb-users] Problem using CalendarTimeT column

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

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

    Justin> As far as I can tell, this is an issue with HDBC's
    Justin> Convertible class/package more than haskelldb. The
    Justin> instance of Convertible for the time type
    Justin> (Data.Time.LocalTime.LocalTime.ZonedTime) is apparently
    Justin> trying to use the format string "%Y-%m-%d %T%Q %z" to
    Justin> parse the data.  Notice %z - that is a time zone
    Justin> specifier, and it does not show up in the example value
    Justin> ("2009-09-28 11:48:37").

    Justin> If you can manage to get your own instance of Convertible
    Justin> defined, you should be able to define your own format
    Justin> string to parse the data.  Alternatively, if you can get
    Justin> SqlLite to include timezone information you can probably
    Justin> get the value to parse.

I suspect the problem is with the insert.

For the SELECT, I can use showSQL to see the generated SQL. How can I
find out what SQL the insert is generating?
-- 
Colin Adams
Preston Lancashire

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

Re: [Haskelldb-users] Problem using CalendarTimeT column

[Justin B. <jgb...@gm...>]

On Tue, Sep 29, 2009 at 8:42 AM, Colin Paul Adams
<co...@co...> wrote:
> But the reason I tried changing to use UTCTime was in answer to my
> original problem, you said I could extract a UTCTime from it (you gave
> an example function).
> So I am puzzled as to how I can get a different type out from what I
> put in.
>

That's understandable and I don't think I was correct anyways. I
mostly use HaskellDB to generate SQL which I execute elsewhere, so I'm
not as familiar with extracting results in Haskell. Sorry for any
confusion!

Justin

Re: [Haskelldb-users] Problem using CalendarTimeT column

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

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

    Justin> The solution is to make "ct" have type CalendarTime, not
    Justin> UTCTime.  HaskellDB uses the deprecated, but still in wide
    Justin> use, old-time package which exposes
    Justin> System.Time.CalendarTime. You can't get a CalendarTime for
    Justin> the current date direclty, but you can indirectly. Try:

    Justin>   clockTime <- getClockTime ct <- toCalendarTime clockTime

So this is what I was originally doing.

    Justin> Or more succinctly:

    Justin>   ct <- getClockTime >>= toCalendarTime


    Justin> Then you should be able to do your insert. Finally, you
    Justin> asked:

    >> How do I control this with a signature?

    Justin> That's not relevant here as I was referring to the
    Justin> conversion of SQL types to Haskell values. Since DBDirect
    Justin> generates the code for your table, you are stuck with
    Justin> CalendarTime.

But the reason I tried changing to use UTCTime was in answer to my
original problem, you said I could extract a UTCTime from it (you gave
an example function).
So I am puzzled as to how I can get a different type out from what I
put in.

But perhaps you thought I was doing the following:
    Justin> DBDirect is just a convenience, though. You could make
    Justin> your own definition by hand.

One of the reasons I'm using HaskellDB is I want to be sure everything
is consistent.

    Justin> haskelldb-th package, which gives some Template Haskell
    Justin> functions for defining tables and columns.  Just FYI.

I'll take a look. Thanks.
-- 
Colin Adams
Preston Lancashire

Re: [Haskelldb-users] Problem using CalendarTimeT column

[Justin B. <jgb...@gm...>]

The generated module is what I wanted. Here is your table definition:

> type ImageTable =
>    (RecCons IndexNumber (Expr Integer)
>     (RecCons Caption (Expr String)
>      (RecCons Thumbnail (Expr String)
>       (RecCons Preview (Expr String)
>        (RecCons Original (Expr (Maybe String))
>         (RecCons UploadTime (Expr CalendarTime) RecNil))))))

Specifically, UploadTime is of tyep "CalendarTime". The error you got was:

>  ct <- getCurrentTime
>  DB.insert db IT.imageTable (<snip> # IT.uploadTime <<- ct)
>
> the compiler complains:
>
>  Couldn't match expected type `System.Time.CalendarTime'
>           against inferred type `UTCTime'
>    In the second argument of `(<<-)', namely `ct'
>    In the second argument of `(#)', namely `IT.uploadTime <<- ct'

The solution is to make "ct" have type CalendarTime, not UTCTime.
HaskellDB uses the deprecated, but still in wide use, old-time package
which exposes System.Time.CalendarTime. You can't get a CalendarTime
for the current date direclty, but you can indirectly. Try:

  clockTime <- getClockTime
  ct <- toCalendarTime clockTime

Or more succinctly:

  ct <- getClockTime >>= toCalendarTime

Then you should be able to do your insert. Finally, you asked:

> How do I control this with a signature?

That's not relevant here as I was referring to the conversion of SQL
types to Haskell values. Since DBDirect generates the code for your
table, you are stuck with CalendarTime.

DBDirect is just a convenience, though. You could make your own
definition by hand. There is also the haskelldb-th package, which
gives some Template Haskell functions for defining tables and columns.
Just FYI.

Justin

Re: [Haskelldb-users] Problem using CalendarTimeT column

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

>>>>> "Colin" == Colin Paul Adams <co...@co...> writes:

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

    Justin> Can you send the type definition for ImageTable (aliased
    Justin> as IT in your code)? That will help me answer your
    Justin> question.

I assumed you meant the DBSpec difinition, but just in case you meant
the generated module, here it is as well:

{-# OPTIONS_GHC -fcontext-stack46 #-}
-- NOTE: use GHC flag -fcontext-stack46 with this module if GHC < 6.8.1
---------------------------------------------------------------------------
-- Generated by DB/Direct
---------------------------------------------------------------------------
module Database.ImageTable where

import Database.HaskellDB.DBLayout

---------------------------------------------------------------------------
-- Table type
---------------------------------------------------------------------------

type ImageTable =
    (RecCons IndexNumber (Expr Integer)
     (RecCons Caption (Expr String)
      (RecCons Thumbnail (Expr String)
       (RecCons Preview (Expr String)
        (RecCons Original (Expr (Maybe String))
         (RecCons UploadTime (Expr CalendarTime) RecNil))))))

---------------------------------------------------------------------------
-- Table
---------------------------------------------------------------------------
imageTable :: Table ImageTable

imageTable = baseTable "imageTable" $
             hdbMakeEntry IndexNumber #
             hdbMakeEntry Caption #
             hdbMakeEntry Thumbnail #
             hdbMakeEntry Preview #
             hdbMakeEntry Original #
             hdbMakeEntry UploadTime

---------------------------------------------------------------------------
-- Fields
---------------------------------------------------------------------------
---------------------------------------------------------------------------
-- IndexNumber Field
---------------------------------------------------------------------------

data IndexNumber = IndexNumber

instance FieldTag IndexNumber where fieldName _ = "indexNumber"

indexNumber :: Attr IndexNumber Integer
indexNumber = mkAttr IndexNumber

---------------------------------------------------------------------------
-- Caption Field
---------------------------------------------------------------------------

data Caption = Caption

instance FieldTag Caption where fieldName _ = "caption"

caption :: Attr Caption String
caption = mkAttr Caption

---------------------------------------------------------------------------
-- Thumbnail Field
---------------------------------------------------------------------------

data Thumbnail = Thumbnail

instance FieldTag Thumbnail where fieldName _ = "thumbnail"

thumbnail :: Attr Thumbnail String
thumbnail = mkAttr Thumbnail

---------------------------------------------------------------------------
-- Preview Field
---------------------------------------------------------------------------

data Preview = Preview

instance FieldTag Preview where fieldName _ = "preview"

preview :: Attr Preview String
preview = mkAttr Preview

---------------------------------------------------------------------------
-- Original Field
---------------------------------------------------------------------------

data Original = Original

instance FieldTag Original where fieldName _ = "original"

original :: Attr Original (Maybe String)
original = mkAttr Original

---------------------------------------------------------------------------
-- UploadTime Field
---------------------------------------------------------------------------

data UploadTime = UploadTime

instance FieldTag UploadTime where fieldName _ = "uploadTime"

uploadTime :: Attr UploadTime CalendarTime
uploadTime = mkAttr UploadTime

-- 
Colin Adams
Preston Lancashire

Re: [Haskelldb-users] Problem using CalendarTimeT column

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

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

    Justin> On Mon, Sep 28, 2009 at 12:33 PM, Colin Paul Adams
    Justin> <co...@co...> wrote:
    >> If I look there, I see Data.Time.Clock.UTCTime, which I guess
    >> is what you are referring to in the line above. But if I try to
    >> write such a record with:
    >> 
    >>  ct <- getCurrentTime  DB.insert db IT.imageTable (<snip> #
    >> IT.uploadTime <<- ct)
    >> 
    >> the compiler complains:
    >> 
    >>  Couldn't match expected type `System.Time.CalendarTime'      
    >>     against inferred type `UTCTime'    In the second argument
    >> of `(<<-)', namely `ct'    In the second argument of `(#)',
    >> namely `IT.uploadTime <<- ct'
    >> 
    >> How do I control this with a signature?

    Justin> Can you send the type definition for ImageTable (aliased
    Justin> as IT in your code)? That will help me answer your
    Justin> question.

-- | Relative URIs for various sized images
imageTable :: TInfo
imageTable = TInfo {tname = "imageTable", cols = [indexColumn,
captionColumn, thumbnailColumn, previewColumn, originalColumn,
uploadTimeColumn]}



captionColumn :: CInfo
captionColumn = CInfo {cname = "caption", descr = (StringT, False)}

indexColumn :: CInfo
indexColumn = CInfo {cname = "indexNumber", descr = (IntegerT, False)}

thumbnailColumn :: CInfo
thumbnailColumn = CInfo {cname = "thumbnail", descr = (StringT, False)}

previewColumn :: CInfo
previewColumn = CInfo {cname = "preview", descr = (StringT, False)}

originalColumn :: CInfo
originalColumn = CInfo {cname = "original", descr = (StringT, True)}

uploadTimeColumn :: CInfo
uploadTimeColumn = CInfo {cname = "uploadTime", descr = (CalendarTimeT, False)}

-- 
Colin Adams
Preston Lancashire

Re: [Haskelldb-users] Problem using CalendarTimeT column

[Justin B. <jgb...@gm...>]

On Mon, Sep 28, 2009 at 12:33 PM, Colin Paul Adams
<co...@co...> wrote:
> If I look there, I see Data.Time.Clock.UTCTime, which I guess is what
> you are referring to in the line above. But if I try to write such a
> record with:
>
>  ct <- getCurrentTime
>  DB.insert db IT.imageTable (<snip> # IT.uploadTime <<- ct)
>
> the compiler complains:
>
>  Couldn't match expected type `System.Time.CalendarTime'
>           against inferred type `UTCTime'
>    In the second argument of `(<<-)', namely `ct'
>    In the second argument of `(#)', namely `IT.uploadTime <<- ct'
>
> How do I control this with a signature?

Can you send the type definition for ImageTable (aliased as IT in your
code)? That will help me answer your question.

> --
> Colin Adams
> Preston Lancashire
>

Re: [Haskelldb-users] Problem using CalendarTimeT column

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

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

    Justin> The type used is not determined by HaskellDB. Your application
    Justin> determines the type it expects, and the appropriate
    Justin> Convertible instance is selected. If that doesn't make
    Justin> sense, what I mean is the type signature of your result
    Justin> determines the type to which the value will be
    Justin> converted. For example, if your function is named
    Justin> "getTimestampRows", you can control the type converted to
    Justin> via it's signature:


    Justin>   getTimestampRows :: IO [Record (Col1 (Expr Day)) ... )]

    Justin> or

    Justin>   getTimestampRows :: IO [Record (Col1 (Expr ZonedTime))
    Justin> ... )]

    Justin> or

    Justin>   getTimestampRows :: IO [Record (Col1 (Expr UTCTime))
    Justin> ... )]

I'm lost here.

HaskellDB is expecting a System.Time.CalendarTime (because I defined
the column as a CalendarT - there was no other choice in the
constructors from FireldDesc), but if I look at
the documentation for System.Time, it says it is deprecated - look at
Data.Time instead.
If I look there, I see Data.Time.Clock.UTCTime, which I guess is what
you are referring to in the line above. But if I try to write such a
record with:

 ct <- getCurrentTime
  DB.insert db IT.imageTable (<snip> # IT.uploadTime <<- ct)

the compiler complains:

  Couldn't match expected type `System.Time.CalendarTime'
           against inferred type `UTCTime'
    In the second argument of `(<<-)', namely `ct'
    In the second argument of `(#)', namely `IT.uploadTime <<- ct'

How do I control this with a signature?
-- 
Colin Adams
Preston Lancashire

Re: [Haskelldb-users] Problem using CalendarTimeT column

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

    Justin> Returning "t" projected every column in the table, which
    Justin> led to the conversion problem. DB.project should always be
    Justin> the last function called, as it filters the columns
    Justin> returned down to just the ones you want.  I moved the
    Justin> order call above the project call so you can still get
    Justin> your results ordered like you want, but it doesn't
    Justin> interfere with the projection.

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

    Justin> Does that do the trick?

Yes, that works.
Thank-you very much.
-- 
Colin Adams
Preston Lancashire

Re: [Haskelldb-users] Problem using CalendarTimeT column

[Justin B. <jgb...@gm...>]

On Mon, Sep 28, 2009 at 10:20 AM, Colin Paul Adams
<co...@co...> wrote:

> Not really, as I am not actually requesting that column (not
> intentioanlly, anyway).
> Probably I have got the project call wrong. My code looks like this:
>
>  let q = do
>        t <- DB.table IT.imageTable
>        DB.project (IT.indexNumber DB.<< t DB.! IT.indexNumber)
>        DB.order [DB.desc t IT.indexNumber]
>        return t
>  rs <- DB.query db q
>  let nextIndex = case null rs of
>                    True -> 1
>                    False -> 1 + (head rs DB.! IT.indexNumber)
>

Rewrite your projection so you aren't returning t:

>  let q = do
>        t <- DB.table IT.imageTable
>        DB.order [DB.desc t IT.indexNumber]
>        DB.project (IT.indexNumber DB.<< t DB.! IT.indexNumber)

Returning "t" projected every column in the table, which led to the
conversion problem. DB.project should always be the last function
called, as it filters the columns returned down to just the ones you
want.  I moved the order call above the project call so you can still
get your results ordered like you want, but it doesn't interfere with
the projection.

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

Does that do the trick?

Justin

Re: [Haskelldb-users] Problem using CalendarTimeT column

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

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

The type used is not determined by HaskellDB. Your application
    Justin> determines the type it expects, and the appropriate
    Justin> Convertible instance is selected. If that doesn't make
    Justin> sense, what I mean is the type signature of your result
    Justin> determines the type to which the value will be
    Justin> converted. For example, if your function is named
    Justin> "getTimestampRows", you can control the type converted to
    Justin> via it's signature:


    Justin>   getTimestampRows :: IO [Record (Col1 (Expr Day)) ... )]

    Justin> or

    Justin>   getTimestampRows :: IO [Record (Col1 (Expr ZonedTime))
    Justin> ... )]

    Justin> or

    Justin>   getTimestampRows :: IO [Record (Col1 (Expr UTCTime))
    Justin> ... )]

    Justin> Does that help?

Not really, as I am not actually requesting that column (not
intentioanlly, anyway).
Probably I have got the project call wrong. My code looks like this:

 let q = do
        t <- DB.table IT.imageTable
        DB.project (IT.indexNumber DB.<< t DB.! IT.indexNumber)
        DB.order [DB.desc t IT.indexNumber]
        return t
  rs <- DB.query db q
  let nextIndex = case null rs of
                    True -> 1
                    False -> 1 + (head rs DB.! IT.indexNumber)

I can't say that I could understand the project function from the
examples I saw. Mu intention was just to retrieve the indexNumber
column, which is defined as an integerT in the FieldDesc.
-- 
Colin Adams
Preston Lancashire

Re: [Haskelldb-users] Problem using CalendarTimeT column

[Justin B. <jgb...@gm...>]

On Mon, Sep 28, 2009 at 9:26 AM, Colin Paul Adams
<co...@co...> wrote:
> Which of the following will HaskellDB be using to CalendarTimeT with
> sqlite-HDBC?
>

The type used is not determined by HaskellDB. Your application
determines the type it expects, and the appropriate Convertible
instance is selected. If that doesn't make sense, what I mean is the
type signature of your result determines the type to which the value
will be converted. For example, if your function is named
"getTimestampRows", you can control the type converted to via it's
signature:


  getTimestampRows :: IO [Record  (Col1 (Expr Day)) ... )]

or

  getTimestampRows :: IO [Record  (Col1 (Expr ZonedTime)) ... )]

or

  getTimestampRows :: IO [Record  (Col1 (Expr UTCTime)) ... )]

Does that help?

> SqlLocalDate Day        Local YYYY-MM-DD (no timezone)
> SqlLocalTimeOfDay TimeOfDay     Local HH:MM:SS (no timezone)
> SqlZonedLocalTimeOfDay TimeOfDay TimeZone       Local HH:MM:SS -HHMM. Converts to and from (TimeOfDay, TimeZone).
> SqlLocalTime LocalTime  Local YYYY-MM-DD HH:MM:SS (no timezone)
> SqlZonedTime ZonedTime  Local YYYY-MM-DD HH:MM:SS -HHMM. Considered equal if both convert to the same UTC time.
> SqlUTCTime UTCTime      UTC YYYY-MM-DD HH:MM:SS
> SqlDiffTime NominalDiffTime     Calendar diff between seconds. Rendered as Integer when converted to String, but greater precision may be preserved for other types or to underlying database.
> SqlPOSIXTime POSIXTime  Time as seconds since midnight Jan 1 1970 UTC. Integer rendering as for SqlDiffTime.
> SqlEpochTime Integer    DEPRECATED Representation of ClockTime or CalendarTime. Use SqlPOSIXTime instead.
> SqlTimeDiff Integer     DEPRECATED Representation of TimeDiff. Use
> SqlDiffTime instead.
>
> --
> Colin Adams
> Preston Lancashire
>

Re: [Haskelldb-users] Problem using CalendarTimeT column

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

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

    Justin> As far as I can tell, this is an issue with HDBC's
    Justin> Convertible class/package more than haskelldb. The
    Justin> instance of Convertible for the time type
    Justin> (Data.Time.LocalTime.LocalTime.ZonedTime) is apparently
    Justin> trying to use the format string "%Y-%m-%d %T%Q %z" to
    Justin> parse the data.  Notice %z - that is a time zone
    Justin> specifier, and it does not show up in the example value
    Justin> ("2009-09-28 11:48:37").

    Justin> If you can manage to get your own instance of Convertible
    Justin> defined, you should be able to define your own format
    Justin> string to parse the data.  Alternatively, if you can get
    Justin> SqlLite to include timezone information you can probably
    Justin> get the value to parse.

Which of the following will HaskellDB be using to CalendarTimeT with
sqlite-HDBC?

SqlLocalDate Day	Local YYYY-MM-DD (no timezone)
SqlLocalTimeOfDay TimeOfDay	Local HH:MM:SS (no timezone)
SqlZonedLocalTimeOfDay TimeOfDay TimeZone	Local HH:MM:SS -HHMM. Converts to and from (TimeOfDay, TimeZone).
SqlLocalTime LocalTime	Local YYYY-MM-DD HH:MM:SS (no timezone)
SqlZonedTime ZonedTime	Local YYYY-MM-DD HH:MM:SS -HHMM. Considered equal if both convert to the same UTC time.
SqlUTCTime UTCTime	UTC YYYY-MM-DD HH:MM:SS
SqlDiffTime NominalDiffTime	Calendar diff between seconds. Rendered as Integer when converted to String, but greater precision may be preserved for other types or to underlying database.
SqlPOSIXTime POSIXTime	Time as seconds since midnight Jan 1 1970 UTC. Integer rendering as for SqlDiffTime.
SqlEpochTime Integer	DEPRECATED Representation of ClockTime or CalendarTime. Use SqlPOSIXTime instead.
SqlTimeDiff Integer	DEPRECATED Representation of TimeDiff. Use
SqlDiffTime instead. 

-- 
Colin Adams
Preston Lancashire

Re: [Haskelldb-users] Problem using CalendarTimeT column

[Justin B. <jgb...@gm...>]

As far as I can tell, this is an issue with HDBC's Convertible
class/package more than haskelldb. The instance of Convertible for the
time type (Data.Time.LocalTime.LocalTime.ZonedTime) is apparently
trying to use the format string "%Y-%m-%d %T%Q %z" to parse the data.
Notice %z - that is a time zone specifier, and it does not show up in
the example value ("2009-09-28 11:48:37").

If you can manage to get your own instance of Convertible defined, you
should be able to define your own format string to parse the data.
Alternatively, if you can get SqlLite to include timezone information
you can probably get the value to parse.

Justin

On Mon, Sep 28, 2009 at 8:34 AM, Colin Paul Adams
<co...@co...> wrote:
>>>>>> "Justin" == Justin Bailey <jgb...@gm...> writes:
>
>    Justin> What driver and database are you using?
>
> Sqlite3 via HDBC.
> --
> Colin Adams
> Preston Lancashire
>

Re: [Haskelldb-users] Problem using CalendarTimeT column

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

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

    Justin> What driver and database are you using?

Sqlite3 via HDBC.
-- 
Colin Adams
Preston Lancashire

Re: [Haskelldb-users] Problem using CalendarTimeT column

[Justin B. <jgb...@gm...>]

What driver and database are you using?

On Mon, Sep 28, 2009 at 8:07 AM, Colin Paul Adams
<co...@co...> wrote:
>>>>>> "Colin" == Colin Paul Adams <co...@co...> writes:
>
>    Colin> I've tried declaring a column of type CalendarTimeT.  I
>    Colin> inserted a record using the variable time, which I got like
>    Colin> this:
>
>    Colin> ct <- getClockTime time <- toCalendarTime ct
>
>    Colin> as that seemd to be the way to produce a calendar time from
>    Colin> reading the docs for System.Time.
>
>    Colin> However, on reading the record back, I get:
>
>    Colin> Server error: Convertible: error converting source data
>    Colin> SqlString "2009-09-28 11:48:37" of type SqlValue to type
>    Colin> Data.Time.LocalTime.LocalTime.ZonedTime: Cannot parse using
>    Colin> default format string "%Y-%m-%d %T%Q %z"
>
>    Colin> What have I missed?
>
> I changed:
>
> time <- toCalendarTime ct
>
> to:
>
> let time = toUTCTime ct
>
> but I still get the same error.
> --
> Colin Adams
> Preston Lancashire
>
> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry&reg; Developer Conference in SF, CA
> is the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9&#45;12, 2009. Register now&#33;
> http://p.sf.net/sfu/devconf
> _______________________________________________
> Haskelldb-users mailing list
> Has...@li...
> https://lists.sourceforge.net/lists/listinfo/haskelldb-users
>

Re: [Haskelldb-users] Problem using CalendarTimeT column

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

>>>>> "Colin" == Colin Paul Adams <co...@co...> writes:

    Colin> I've tried declaring a column of type CalendarTimeT.  I
    Colin> inserted a record using the variable time, which I got like
    Colin> this:

    Colin> ct <- getClockTime time <- toCalendarTime ct

    Colin> as that seemd to be the way to produce a calendar time from
    Colin> reading the docs for System.Time.

    Colin> However, on reading the record back, I get:

    Colin> Server error: Convertible: error converting source data
    Colin> SqlString "2009-09-28 11:48:37" of type SqlValue to type
    Colin> Data.Time.LocalTime.LocalTime.ZonedTime: Cannot parse using
    Colin> default format string "%Y-%m-%d %T%Q %z"

    Colin> What have I missed?

I changed:

time <- toCalendarTime ct

to:

let time = toUTCTime ct

but I still get the same error.
-- 
Colin Adams
Preston Lancashire

[Haskelldb-users] Problem using CalendarTimeT column

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

I've tried declaring a column of type CalendarTimeT. 

I inserted a record using the variable time, which I got like this:

ct <- getClockTime
time <- toCalendarTime ct

as that seemd to be the way to produce a calendar time from reading
the docs for System.Time.

However, on reading the record back, I get:

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

What have I missed?
-- 
Colin Adams
Preston Lancashire

[Haskelldb-users] HaskellDB v1.0 upgrade guide

[Justin B. <jgb...@gm...>]

I've checked in a file named "UPGRADE" to the repo. It lists all the
breaking changes between haskelldb v0.12 and the upcoming v1.0
version. The repo isn't its final home but for now it will serve.
Feedback and/or patches welcome!

Justin

Re: [Haskelldb-users] Identity columns

[Justin B. <jgb...@gm...>]

Sure, there is a "_max" operator which you can use:

  http://hackage.haskell.org/packages/archive/haskelldb/0.12/doc/html/Database-HaskellDB-Query.html#v%3A_max

On Thu, Sep 17, 2009 at 8:53 AM, Colin Paul Adams
<co...@co...> wrote:
>>>>>> "Justin" == Justin Bailey <jgb...@gm...> writes:
>
>    Justin> In that case I would use the database driver's support for
>    Justin> returning auto-increment columns. You'll want to look at
>    Justin> the specific library you are using to see how to do
>    Justin> that. The HaskellDB interface may hide that from you - I'm
>    Justin> not sure. You may want to use HaskellDB just to generate
>    Justin> the appropriate SQL, which you then execute using the DB
>    Justin> library.
>
> This isn't going to work.
>
> Can I get the MAX of a column using haskelldb syntax?
> --
> Colin Adams
> Preston Lancashire
>

Re: [Haskelldb-users] Identity columns

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

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

    Justin> In that case I would use the database driver's support for
    Justin> returning auto-increment columns. You'll want to look at
    Justin> the specific library you are using to see how to do
    Justin> that. The HaskellDB interface may hide that from you - I'm
    Justin> not sure. You may want to use HaskellDB just to generate
    Justin> the appropriate SQL, which you then execute using the DB
    Justin> library.

This isn't going to work.

Can I get the MAX of a column using haskelldb syntax?
-- 
Colin Adams
Preston Lancashire