problems with sql-timestamp and date*

[George Neuner]

Hi all,

Got a wierd problem handling dates.   I am retrieving a UTC timestamp from a Postgresql (9.3.9) database, converting it to a  date* and then offsetting to a (variable) time zone.


eliding a lot of unrelated code:

    :
    (require (only-in  srfi/19  date->string)
             (except-in racket/date date->string))
    :
    (set! result (query-row db sql-cmd  userid ))
    :
    (set! expires (vector-ref result 1))
(eprintf "=> expires ~s~n" expires)
    (set! expires (sql-datetime->srfi-date expires))
(eprintf "=> expires ~s~n" expires)
    :
    (let* [
           ; convert UTC expire time to user's timezone
           (expires (seconds->date (date*->seconds expires #f)
                                    timezone))
           :
          ]
(eprintf "=> expires ~s~n" expires)
        :

with 'timezone'  =  -4  (EDT)  this produces:

=> expires #(struct:sql-timestamp 2015 8 22 20 26 49 585512000 0)
=> expires #(struct:date* 49 26 20 22 8 2015 6 233 #f 0 585512000 "")
=> expires #(struct:date* 34 36 16 22 8 2015 6 233 #t -14400 512000000 "Eastern Daylight Time")


The results are NOT repeatable ... e.g., another run produces:

=> expires #(struct:sql-timestamp 2015 8 22 20 57 40 483091000 0)
=> expires #(struct:date* 40 57 20 22 8 2015 6 233 #f 0 483091000 "")
=> expires #(struct:date* 43 5 17 22 8 2015 6 233 #t -14400 91000000 "Eastern Daylight Time")

and yet another run:

=> expires #(struct:sql-timestamp 2015 8 22 21 1 21 729173000 0)
=> expires #(struct:date* 21 1 21 22 8 2015 6 233 #f 0 729173000 "")
=> expires #(struct:date* 30 13 17 22 8 2015 6 233 #t -14400 173000000 "Eastern Daylight Time")

As you can see, it seems that sql-timestamp->srfi-date sometimes botches the conversion, and it seems that (seconds->date (date*->seconds)) sometimes is not symmetric.   BIG BUT ... this ONLY happens when the sql-timestamp comes from an actual database.  This simple test program :

(require
  racket/date
  db
  db/util/datetime
  )

(define  db-time  (sql-timestamp 2015 8 22 18 48 12 0 #f))
(define  rkt-time (sql-datetime->srfi-date db-time))

(define timezone -4)
(define lcl-time (seconds->date (date*->seconds rkt-time #f) timezone))

(printf "DB : ~s~n" db-time )
(printf "RKT: ~s~n" rkt-time )
(printf "LCL: ~s~n" lcl-time )

correctly produces

DB : #(struct:sql-timestamp 2015 8 22 18 48 12 0 #f)
RKT: #(struct:date* 12 48 18 22 8 2015 6 233 #f 0 0 "")
LCL: #(struct:date* 12 48 14 22 8 2015 6 233 #t -14400 0 "Eastern Daylight Time")


Can anyone shed light on what's happening?  I really need these copnverted timestamps to be correct.

Thanks,
George

[George Neuner]

Ok, reading the docs more carefully, I realized that  seconds->date  takes a boolean and not a time zone offset, but that doesn't explain why sql-timestamp->srfi-date is not working properly.

I'm now offsetting the time with

(seconds->date
    (+ (date*->seconds ts  #f) (* timezone 60 60))
     #f))

which gives me the right time, but in the wrong time zone.

Just how do I offset time zones properly?   Do I need to construct the date* piecemeal?

Thanks,
George

[Jon Zeppieri]

Where exactly do you see sql-timestamp->srfi-date failing? In your
examples, what I'm seeing is incorrect translation from UTC to UTC-4,
but I don't see where the translation from sql-timestamp to date* is
going wrong. Could you point to exactly where you see the problem?

As far as offsetting time zones properly, Racket's built-in libraries
will not help you there. You might want to check out my library,
gregor: http://pkg-build.racket-lang.org/doc/gregor/index.html. You'd
still need to write your own sql-datetime->moment function, but that's
not difficult. In fact:

(require gregor)
(require racket/match)

(define (sql-timestamp->moment t)
(match-define (sql-timestamp y mo d h mi s n tz) t)

(moment y mo d h mi s n #:tz (or tz 0)))

Unfortunately, since sql-timestamp only represents time zones as
offsets from UTC, you're a bit limited from the start. Gregor
certainly can use UTC offsets as TZs but prefers to use IANA names
(e.g., "America/New_York").

-Jon

> --
> 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...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Jon Zeppieri]

On Sat, Aug 22, 2015 at 4:02 PM, Jon Zeppieri <zepp...@gmail.com> wrote:
>
>
> (require gregor)
> (require racket/match)
>
> (define (sql-timestamp->moment t)
> (match-define (sql-timestamp y mo d h mi s n tz) t)
>
> (moment y mo d h mi s n #:tz (or tz 0)))

Actually, a sql-timestamp with a #f tz means that the timestamp
doesn't have any zone info, so that would actually correspond to
gregor's `datetime` (instead of `moment`, which includes tz info).

-Jon

[George Neuner]

A little more experimenting shows that  changing  date*->seconds  to  date->seconds  (no asterisk) in

(seconds->date
    (+ (date*->seconds ts  #f) (* timezone 60 60))
     #f))

guarantees the time offset always is correct wrt the original UTC time.  So it seems that  (seconds->date (date*->seconds date #f) #f)  is not always symmetric?   sql-timestamp->srfi-date produces a date* so I thought I needed to use  date*->seconds.

But I still have the problem that  sql-timestamp->srfi-date isn't always converting the SQL timestamp properly.

George

[George Neuner]

On 8/22/2015 4:02 PM, Jon Zeppieri wrote:

Where exactly do you see sql-timestamp->srfi-date failing? In your
examples, what I'm seeing is incorrect translation from UTC to UTC-4,
but I don't see where the translation from sql-timestamp to date* is
going wrong. Could you point to exactly where you see the problem?

Hi Jon,

I believe now I was mistaken about  sql-timestamp->srfi-date.  Recompiling my project seems to have fixed that - I am now getting completely consistent results from the conversion.


However, I am still confused by the difference between

(seconds->date
    (+ (date*->seconds expires #f) (* timezone 60 60))
     #f))

and

(seconds->date
    (+ (date->seconds expires #f) (* timezone 60 60))
     #f))

The latter code using date works properly (modulo the time zone field) and gives consistent results, but the former using date* gives inconsistent results.

 E.g.,: with timezone = -5

=> expires #(struct:sql-timestamp 2015 8 22 21 56 33 805346000 0)
=> expires #(struct:date* 33 56 21 22 8 2015 6 233 #f 0 805346000 "")
=> expires #(struct:date* 58 9 17 22 8 2015 6 233 #f 0 346000000 "UTC")

=> expires #(struct:sql-timestamp 2015 8 22 22 23 45 95751000 0)
=> expires #(struct:date* 45 23 22 22 8 2015 6 233 #f 0 95751000 "")
=> expires #(struct:date* 20 25 17 22 8 2015 6 233 #f 0 751000000 "UTC")


Recompiling does not fix this.  It seems like  date*->seconds  and  seconds->date  are not symmetric.   However, sql-timestamp->srfi-date produces a date*, so I thought I should being using  date*->seconds.

BTW: this is in 6.1.1  (forgot to mention that previously).

George

[Jon Zeppieri]

On Sat, Aug 22, 2015 at 4:36 PM, George Neuner <gneu...@comcast.net> wrote:
>
> The latter code using date works properly (modulo the time zone field) and
> gives consistent results, but the former using date* gives inconsistent
> results.
>
> E.g.,: with timezone = -5
>
> => expires #(struct:sql-timestamp 2015 8 22 21 56 33 805346000 0)
> => expires #(struct:date* 33 56 21 22 8 2015 6 233 #f 0 805346000 "")
> => expires #(struct:date* 58 9 17 22 8 2015 6 233 #f 0 346000000 "UTC")
>
> => expires #(struct:sql-timestamp 2015 8 22 22 23 45 95751000 0)
> => expires #(struct:date* 45 23 22 22 8 2015 6 233 #f 0 95751000 "")
> => expires #(struct:date* 20 25 17 22 8 2015 6 233 #f 0 751000000 "UTC")
>

These both look wrong to me. Maybe I'm confused, but my understanding
is that the database timestamp is in UTC, and you want a date*
representing the same point in time, but in specified UTC offset. In
other words, Your first example here starts with
2015-08-22T21:56:33.805346Z. Your end result is
2015-08-22T17:09:58.346, which is clearly not exactly 5 hours offset.

In the second example, you start with 2015-08-22T22:23:45.95751000Z
and wind up with 2015-08-22T17:25:20.751, which is also wrong, but by
a considerably smaller margin. I actually can't reproduce the problem
in the second example, but I'm also running 6.2.0.2. It looks to me
like date->seconds roundtrips correctly w.r.t. seconds->date but
date*->seconds does not:
```
> d
(date* 33 56 21 22 8 2015 6 233 #f 0 805346000 "")
> (seconds->date (+ (* -5 60 60) (date->seconds d #f)) #f)
(date* 33 56 16 22 8 2015 6 233 #f 0 0 "UTC") ;; correct, modulo nanoseconds
> (seconds->date (+ (* -5 60 60) (date*->seconds d #f)) #f)
(date* 58 9 17 22 8 2015 6 233 #f 0 346000000 "UTC") ;; incorrect
```

So, yeah, I'd say that there's a bug in date*->seconds:
```
> (date->seconds d #f)
1440280593
> (date*->seconds d #f)
1440281398 173/500
```

Let's see what this looks like in gregor's terms:
```
> (define d (moment 2015 8 22 21 56 33 805346000 #:tz "UTC"))
> d
#<moment 2015-08-22T21:56:33.805346Z[UTC]>
> (adjust-timezone d (* -5 60 60))
#<moment 2015-08-22T16:56:33.805346-05:00>
> (->posix d)
1440280593 402673/500000
```

-Jon

[Jon Zeppieri]

On Sat, Aug 22, 2015 at 5:50 PM, Jon Zeppieri <zepp...@gmail.com> wrote:
> On Sat, Aug 22, 2015 at 4:36 PM, George Neuner <gneu...@comcast.net> wrote:

[Sorry, I bungled the this part of the email.]

> Maybe I'm confused, but my understanding
> is that the database timestamp is in UTC, and you want a date*
> representing the same point in time, but in specified UTC offset. In
> other words, Your first example here starts with

First, instead of "in specified UTC offset," it should read, "with the
specified UTC offset [in your example, UTC-5]."
Second, I have no idea what I was going to write after "In other words." :)

-Jon

[George Neuner]

On 8/22/2015 5:50 PM, Jon Zeppieri wrote:

On Sat, Aug 22, 2015 at 4:36 PM, George Neuner <gneu...@comcast.net> wrote:
>
> The latter code using date works properly (modulo the time zone field) and
> gives consistent results, but the former using date* gives inconsistent
> results.
>
>  E.g.,: with timezone = -5
>
> => expires #(struct:sql-timestamp 2015 8 22 21 56 33 805346000 0)
> => expires #(struct:date* 33 56 21 22 8 2015 6 233 #f 0 805346000 "")
> => expires #(struct:date* 58 9 17 22 8 2015 6 233 #f 0 346000000 "UTC")
>
> => expires #(struct:sql-timestamp 2015 8 22 22 23 45 95751000 0)
> => expires #(struct:date* 45 23 22 22 8 2015 6 233 #f 0 95751000 "")
> => expires #(struct:date* 20 25 17 22 8 2015 6 233 #f 0 751000000 "UTC")
>

These both look wrong to me. Maybe I'm confused, but my understanding
is that the database timestamp is in UTC, and you want a date*
representing the same point in time, but in specified UTC offset.

Yes.  I need to render the dates/times into a client provided time zone.

  :

So, yeah, I'd say that there's a bug in date*->seconds:

  :

Thanks for confirming that - it was driving me crazy. 

I have changed to using  date->seconds  everywhere and it seems to be working correctly.   Now I just have to figure out how to get the right value in the time zone field.  I guess there's no way to do that without constructing a new date structure?

George

[Ryan Culpepper]

On 08/22/2015 06:18 PM, George Neuner wrote:
> On 8/22/2015 5:50 PM, Jon Zeppieri wrote:
>> On Sat, Aug 22, 2015 at 4:36 PM, George Neuner<gneu...@comcast.net> wrote:
>> >
>> > The latter code using date works properly (modulo the time zone field) and
>> > gives consistent results, but the former using date* gives inconsistent
>> > results.
>> >
>> > E.g.,: with timezone = -5
>> >
>> > => expires #(struct:sql-timestamp 2015 8 22 21 56 33 805346000 0)
>> > => expires #(struct:date* 33 56 21 22 8 2015 6 233 #f 0 805346000 "")
>> > => expires #(struct:date* 58 9 17 22 8 2015 6 233 #f 0 346000000 "UTC")
>> >
>> > => expires #(struct:sql-timestamp 2015 8 22 22 23 45 95751000 0)
>> > => expires #(struct:date* 45 23 22 22 8 2015 6 233 #f 0 95751000 "")
>> > => expires #(struct:date* 20 25 17 22 8 2015 6 233 #f 0 751000000 "UTC")
>> >
>>
>> These both look wrong to me. Maybe I'm confused, but my understanding
>> is that the database timestamp is in UTC, and you want a date*
>> representing the same point in time, but in specified UTC offset.
>
> Yes. I need to render the dates/times into a client provided time zone.
>
>> So, yeah, I'd say that there's a bug in date*->seconds:
>
> Thanks for confirming that - it was driving me crazy.

The bug in date*->seconds appears to be that it's treating the
nanoseconds as if they were microseconds. I'll fix that.

Ryan