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 -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.
