X-Git-Url: http://git.kpe.io/?a=blobdiff_plain;f=tests%2Ftest-time.lisp;h=a06f1547fe14f2dacecbb633e23947860b3a1448;hb=refs%2Fheads%2F6.8.0;hp=bf2085e9235ac17d897a22541e03f3b2796bb761;hpb=6f5877c86265e4cbf3bf3904a287c46eb1211a32;p=clsql.git diff --git a/tests/test-time.lisp b/tests/test-time.lisp index bf2085e..a06f154 100644 --- a/tests/test-time.lisp +++ b/tests/test-time.lisp @@ -5,10 +5,17 @@ ;;; Test time functions (time.lisp) (in-package #:clsql-tests) -#.(clsql-sys:locally-enable-sql-reader-syntax) +(clsql-sys:file-enable-sql-reader-syntax) (def-view-class datetest () - ((testtimetz :column "testtimetz" + ((id :column "id" + :type integer + :db-kind :key + :db-constraints (:not-null :unique) + :accessor id :initarg :id + :initform nil + :db-type "int4") + (testtimetz :column "testtimetz" :type clsql-sys:wall-time :db-kind :base :db-constraints nil @@ -30,7 +37,7 @@ (def-dataset *cross-platform-datetest* (:setup (lambda () (create-table [datetest] - '(([testtime] wall-time))))) + '(([testtime] "timestamp"))))) (:cleanup (lambda () (drop-table [datetest])))) @@ -43,32 +50,57 @@ (deftest :time/iso-parse/0 (let* ((time1 (parse-timestring "2010-01-23"))) (decode-time time1)) - 0 0 0 0 23 1 2010 6) + 0 0 0 0 23 1 2010 6 nil) (deftest :time/iso-parse/1 (let* ((time1 (parse-timestring "2010-01-23T14:56:32Z"))) (decode-time time1)) - 0 32 56 14 23 1 2010 6) + 0 32 56 14 23 1 2010 6 T) (deftest :time/iso-parse/2 (let* ((time1 (parse-timestring "2008-02-29 12:46:32"))) (decode-time time1)) - 0 32 46 12 29 2 2008 5) + 0 32 46 12 29 2 2008 5 nil) (deftest :time/iso-parse/3 (let* ((time1 (parse-timestring "2010-01-23 14:56:32.44"))) (decode-time time1)) - 440000 32 56 14 23 1 2010 6) + 440000 32 56 14 23 1 2010 6 nil) (deftest :time/iso-parse/4 (let* ((time1 (parse-timestring "2010-01-23 14:56:32.0044"))) (decode-time time1)) - 4400 32 56 14 23 1 2010 6) + 4400 32 56 14 23 1 2010 6 nil) (deftest :time/iso-parse/5 (let* ((time1 (parse-timestring "2010-01-23 14:56:32.000003"))) (decode-time time1)) - 3 32 56 14 23 1 2010 6) + 3 32 56 14 23 1 2010 6 nil) + +(deftest :time/iso-parse/6 + (let* ((time1 (parse-timestring "2010-01-23T14:56:32-05"))) + (decode-time time1)) + 0 32 56 19 23 1 2010 6 t) + +(deftest :time/iso-parse/7 + (let* ((time1 (parse-timestring "2010-01-23T14:56:32-05"))) + (decode-time time1)) + 0 32 56 19 23 1 2010 6 t) + +(deftest :time/iso-parse/8 + (let* ((time1 (parse-timestring "2010-01-23T14:56:32-05:30"))) + (decode-time time1)) + 0 32 26 20 23 1 2010 6 t) + +(deftest :time/utc-round-trip/1 + (let* ((time1 (parse-timestring "2010-01-23T14:56:32"))) + (decode-time (time-to-localtime (time-to-utc time1)))) + 0 32 56 14 23 1 2010 6 nil) + +(deftest :time/utc-round-trip/2 + (let* ((time1 (parse-timestring "2010-01-23T14:56:32Z"))) + (decode-time (time-to-utc (time-to-localtime time1)))) + 0 32 56 14 23 1 2010 6 t) (deftest :time/print-parse/1 ;;make sure when we print and parse we get the same time. @@ -77,7 +109,7 @@ (string-time (iso-timestring time)) (time2 (parse-timestring string-time))) (decode-time time2)) - 0 44 15 14 4 1 2010 1) + 0 44 15 14 4 1 2010 1 nil) (deftest :time/print-parse/2 ;;make sure when we print and parse we get the same time. @@ -86,7 +118,7 @@ (string-time (iso-timestring time)) (time2 (parse-timestring string-time))) (decode-time time2)) - 3 44 15 14 4 1 2010 1) + 3 44 15 14 4 1 2010 1 nil) ;; relations of intervals @@ -296,7 +328,6 @@ ;;; The cross platform dataset uses the 'timestamp' column type which is ;;; in sql-92, for all that means. - (deftest :time/cross-platform/no-usec/no-tz (with-dataset *cross-platform-datetest* (let ((time (parse-timestring "2008-09-09T14:37:29"))) @@ -305,12 +336,14 @@ :values (list time)) (let ((testtime (first (clsql:select [testtime] - :from [datetest] :flatp T + :from [datetest] :flatp t :where [= [testtime] time] )))) (format-time nil (parse-timestring testtime) :format :iso) ))) #.(format-time nil (parse-timestring "2008-09-09T14:37:29") :format :iso)) + ;; I think the reasonable thing is that timezones be stripped and dates be + ;; converted to UTC, as the DB should be returning a zoneless stamp (deftest :time/cross-platform/no-usec/tz (with-dataset *cross-platform-datetest* (let ((time (parse-timestring "2008-09-09T14:37:29-04:00"))) @@ -319,11 +352,13 @@ :values (list time)) (let ((testtime (first (clsql:select [testtime] - :from [datetest] :flatp T + :from [datetest] :flatp t :where [= [testtime] time] )))) (format-time nil (parse-timestring testtime) :format :iso) ))) - #.(format-time nil (parse-timestring "2008-09-09T14:37:29-04:00") :format :iso)) + ;; I think the reasonable thing is that timezones be stripped, as the DB should + ;; be returning a zoneless stamp + #.(format-time nil (parse-timestring "2008-09-09T18:37:29") :format :iso)) ;;;This test gets at the databases that only support miliseconds, ;;; not microseconds. @@ -335,7 +370,7 @@ :values (list time)) (let ((testtime (first (clsql:select [testtime] - :from [datetest] :flatp T + :from [datetest] :flatp t :where [= [testtime] time] )))) (format-time nil (parse-timestring testtime) :format :iso) ))) @@ -349,7 +384,7 @@ :values (list time)) (let ((testtime (first (clsql:select [testtime] - :from [datetest] :flatp T + :from [datetest] :flatp t :where [= [testtime] time] )))) (format-time nil (parse-timestring testtime) :format :iso) ))) @@ -364,11 +399,11 @@ (let ((testtime (first (clsql:select [testtime] :from [datetest] - :limit 1 :flatp T + :limit 1 :flatp t :where [= [testtime] time] )))) (format-time nil (parse-timestring testtime) :format :iso) ))) - #.(format-time nil (parse-timestring "2008-09-09T14:37:29.000213-04:00") :format :iso)) + #.(format-time nil (parse-timestring "2008-09-09T18:37:29.000213") :format :iso)) @@ -376,7 +411,8 @@ ;;; All odbc databases use local times exclusively (they do not send timezone info) ;;; Postgresql can use timezones, except when being used over odbc. This test when ;;; run through both postgres socket and postgres odbc should test a fairly -;;; broad swath of available problem space +;;; broad swath of available problem space, Timestamptz should return UTC times, +;;; timestamps should return zoneless local times ;;; ;;; Things the following tests try to prove correct ;;; * Reading and writing usec and usec-less times @@ -390,43 +426,43 @@ (with-dataset *ds-datetest* (let ((time (parse-timestring "2008-09-09T14:37:29.000213-04:00"))) (clsql-sys:insert-records :into [datetest] - :attributes '([testtimetz] [testtime]) - :values (list time time)) + :attributes '([testtimetz] [testtime] [id]) + :values (list time time 1)) (destructuring-bind (testtimetz testtime) (first (clsql:select [testtimetz] [testtime] :from [datetest] - :limit 1 :flatp T + :limit 1 :flatp t :where [= [testtime] time] )) (values (iso-timestring (parse-timestring testtime)) (iso-timestring (parse-timestring testtimetz)))))) - #.(iso-timestring (parse-timestring "2008-09-09T14:37:29.000213-04:00")) + #.(iso-timestring (parse-timestring "2008-09-09T18:37:29.000213")) #.(iso-timestring (parse-timestring "2008-09-09T14:37:29.000213-04:00"))) (deftest :time/pg/oodml/no-usec (with-dataset *ds-datetest* (let ((time (parse-timestring "2008-09-09T14:37:29-04:00"))) (clsql-sys:update-records-from-instance - (make-instance 'datetest :testtimetz time :testtime time)) + (make-instance 'datetest :testtimetz time :testtime time :id 1)) (let ((o (first (clsql:select 'datetest - :limit 1 :flatp T + :limit 1 :flatp t :where [= [testtime] time] )))) (assert o (o) "o shouldnt be null here (we should have just inserted)") (update-records-from-instance o) (update-instance-from-records o) (values (iso-timestring (testtime o)) (iso-timestring (testtimetz o)))))) - #.(iso-timestring (parse-timestring "2008-09-09T14:37:29-04:00")) + #.(iso-timestring (parse-timestring "2008-09-09T18:37:29")) #.(iso-timestring (parse-timestring "2008-09-09T14:37:29-04:00"))) (deftest :time/pg/oodml/usec (with-dataset *ds-datetest* (let ((time (parse-timestring "2008-09-09T14:37:29.000278-04:00"))) (clsql-sys:update-records-from-instance - (make-instance 'datetest :testtimetz time :testtime time)) + (make-instance 'datetest :testtimetz time :testtime time :id 1)) (let ((o (first (clsql:select 'datetest - :limit 1 :flatp T + :limit 1 :flatp t :where [= [testtime] time] )))) (assert o (o) "o shouldnt be null here (we should have just inserted)") (update-records-from-instance o) @@ -434,10 +470,79 @@ (values (iso-timestring (testtime o)) (iso-timestring (testtimetz o))) ))) - #.(iso-timestring (parse-timestring "2008-09-09T14:37:29.000278-04:00")) + #.(iso-timestring (parse-timestring "2008-09-09T18:37:29.000278")) #.(iso-timestring (parse-timestring "2008-09-09T14:37:29.000278-04:00"))) +(deftest :time/historic-datetimes + (with-dataset *cross-platform-datetest* + (let ((time (parse-timestring "1800-09-09T14:37:29"))) + (clsql-sys:insert-records :into [datetest] + :attributes '([testtime]) + :values (list time)) + (let ((testtime + (first (clsql:select [testtime] + :from [datetest] :flatp t + :where [= [testtime] time] )))) + (format-time nil (parse-timestring testtime) :format :iso) + ))) + #.(format-time nil (parse-timestring "1800-09-09T14:37:29") :format :iso)) + + + +(deftest :time/to-utc/0 + (let* ((clsql-sys::*default-timezone* 5) + (clsql-sys::*default-timezone-is-dst?* nil) + (ts (parse-timestring "2018-02-08T15:43:29-05")) + (utc-ts (time-to-utc ts)) + (lt-ts (time-to-localtime utc-ts)) + (lt-again (time-to-localtime (time-to-utc lt-ts)))) + (values + (format-time nil utc-ts :format :iso) + (format-time nil lt-ts :format :iso) + (format-time nil lt-again :format :iso))) + #.(format-time nil (parse-timestring "2018-02-08T20:43:29Z") :format :iso) + #.(format-time nil (parse-timestring "2018-02-08T15:43:29") :format :iso) + #.(format-time nil (parse-timestring "2018-02-08T15:43:29") :format :iso) + + ) + +(deftest :time/to-utc/1 + (let* ((clsql-sys::*default-timezone* 5) + (clsql-sys::*default-timezone-is-dst?* t) + (ts (parse-timestring "2018-08-08T16:43:29-04")) + (utc-ts (time-to-utc ts)) + (lt-ts (time-to-localtime utc-ts)) + (lt-again (time-to-localtime (time-to-utc lt-ts)))) + (values + (format-time nil utc-ts :format :iso) + (format-time nil lt-ts :format :iso) + (format-time nil lt-again :format :iso))) + #.(format-time nil (parse-timestring "2018-08-08T20:43:29Z") :format :iso) + #.(format-time nil (parse-timestring "2018-08-08T16:43:29") :format :iso) + #.(format-time nil (parse-timestring "2018-08-08T16:43:29") :format :iso) + + ) + +(deftest :time/to-utc/2 + (let* ((clsql-sys::*default-timezone* -1) + (clsql-sys::*default-timezone-is-dst?* nil) + (ts (parse-timestring "2018-02-08T16:43:29+01")) + (utc-ts (time-to-utc ts)) + (lt-ts (time-to-localtime utc-ts)) + (lt-again (time-to-localtime (time-to-utc lt-ts)))) + (values + (format-time nil utc-ts :format :iso) + (format-time nil lt-ts :format :iso) + (format-time nil lt-again :format :iso))) + #.(format-time nil (parse-timestring "2018-02-08T15:43:29Z") :format :iso) + #.(format-time nil (parse-timestring "2018-02-08T16:43:29") :format :iso) + #.(format-time nil (parse-timestring "2018-02-08T16:43:29") :format :iso) + + ) )) -#.(clsql-sys:locally-disable-sql-reader-syntax) + + + +