Hi all,
I'm found something strange with Racket 6.1.1 and Postgresql (9.3.6):
SQL that works differently in a Racket query than in the DB console.
I have a table defined as
create table blah
(
a char(15),
b char(15),
created timestamp with time zone,
expires timestamp with time zone
);
and an insert query
(set! sql-cmd (string-join `(
"insert into blah (a,b,created,expires)"
" values ( $1"
" ,$2"
" ,current_date"
" ,current_date + interval '" ,(number->string
(expire-days)) " days'"
" )"
)))
(query-exec db sql-cmd "some_characters" "more_characters" )
where the value of (expire-days) is 30.
When I execute this in Racket, the dates entered into the table are off
by 1 day and have an erroneous hour component: e.g., today is 7-20, the
values entered are created = 2015-07-19 20:00:00-04 and expires =
2015-08-18 20:00:00-04.
Obviously the problem is with current_date, however ...
When I execute the analogous code directly from the DB console
insert into stats.surveys_restrict (a,b,created,expires)
values ('some_characters'
,'more_characters'
,current_date
,current_date + interval '30 days')
I get the expected values: created = 2015-07-20 00:00:00-04 and
expires = 2015-08-19 00:00:00-04.
Since the dates are in the SQL code rather than passed arguments to the
Racket query call, I don't understand how this is happening ... I would
have expected to see the same behavior in the console. Casting the dates
to type timestamptz in the SQL doesn't change the results (in either
Racket or the console).
If I substitute current_timestamp instead of current_date, the query
works as expected both in Racket and in the console. However, the
Postgresql docs say that current_date is coerced to timestamp and the
behavior of the console seems to confirm that. I don't understand why I
get a different value from the Racket query.
This isn't a I problem per se as I can substitute current_timestamp
without issue, but since the program logic currently doesn't need the
time component, I had thought to use current_date instead.
Any clues as to what is happening?
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.
