Hi all, Using Racket 6.6.
I have a web app where the client provides a range of dates to match for a database search. The dates are provided in ISO8601 YYYY-MM-DD format, and there may be either: - a start and end date - a date and a span specified as a count [maybe negative] of intervals where an interval may be a day, week or month The client also supplies the time zone (numeric) to apply to the search. The server keeps time in UTC, but must deliver results based on time in the client's location. Because of the vaguries of date/time calculations, I'm using Postgresql itself to apply the time zone and, in the 2nd case, to compute the offset and missing date. The problem I'm having is that when I query from Racket, the result sql-timestamps have the wrong time. They come back with the time zone set to UTC, and the hours adjusted ... which would be fine IFF the adjustment was correct, but it isn't. Apologies for formatting - I'm trying to keep the lines short so they don't wrap [much] for Usenet. I've omitted some details ... a working test program is attached so you can play with it. ------------------- The following in Postgresql using the query tool works as expected. e.g., set session time zone -5; with vars (d1,d2) as (select '2016-05-01','2016-06-12') select (d1::text)::timestamptz ,(d2::text)::timestamptz from vars; => 2016-05-01 00:00:00-05, 2016-06-12 00:00:00-05 Whatever time zone is selected for the session, the date comes back with that zone set. ------------------- The following in Racket gets it wrong. e.g., (require srfi/19 ;date and time db db/util/datetime db/util/postgresql ) (let [ (date1 "2016-05-01") (date2 "2016-06-12") (zone "-5") ] (query-exec db (string-append "set session time zone " zone)) (set! sql-cmd (string-join `( ; <- quasiquoted "with" " dates (d1,d2) as" " (select ($1::text)::timestamptz" " ,($2::text)::timestamptz)" "select d1,d1::text" " ,d2,d2::text" " from dates" ))) (set! result (query-row db sql-cmd date1 date2)) (for [(v (in-vector result))] (cond ([sql-timestamp? v] (printf "~s -> ~s~n" v (date->string (sql-datetime->srfi-date v) "~1 ~2")) ) (else (printf "~s -> ~s~n" v (srfi-date->sql-timestamp-tz (string->date v "~Y~m~d ~H~M~S~z"))) ))) ) => #(struct:sql-timestamp 2016 5 1 5 0 0 0 0) -> "2016-05-01 05:00:00Z" "2016-05-01 00:00:00-05" -> #(struct:sql-timestamp 2016 5 1 0 0 0 0 -18000) #(struct:sql-timestamp 2016 6 12 5 0 0 0 0) -> "2016-06-12 05:00:00Z" "2016-06-12 00:00:00-05" -> #(struct:sql-timestamp 2016 6 12 0 0 0 0 -18000) I've converted the output in both directions to show the difference. As you can see, the date returned as text is correct, but the date returned as a sql-timestamp value is wrong. ------------------- Obviously I can work around the problem by returning dates as text and converting them in Racket. But just as clearly, there seems to be a problem somewhere in the db code. George -- You received this message because you are subscribed to the Google Groups "Racket Users" group. To unsubscribe from this group and stop receiving emails from it, send an email to racket-users+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.