You might be ending up with different timezones set on the different
connections, as libpq clients can automatically set/change some
session settings that the racket library does not. I'd try running
"show timezone" via both connections, and see what you end up with.
On Mon, Jul 20, 2015 at 12:29 PM, George Neuner <gneun...@comcast.net> wrote:
> 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.
--
Jay Kominek
--
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.
