pgator.db.pq.types.time source code

1 // Written in D programming language
2 /**
3 *   PostgreSQL time types binary format.
4 *   
5 *   There are following supported libpq formats:
6 *   <ul>
7 *   <li>$(B date) - handles year, month, day. Corresponding D type - $(B std.datetime.Date).</li>
8 *   <li>$(B abstime) - unix time in seconds without timezone. Corresponding D type - $(B PGAbsTime) 
9 *        wrapper around $(B std.datetime.SysTime).</li>
10 *   <li>$(B reltime) - seconds count positive or negative for representation of time durations.
11 *        Corresponding D type - $(B PGRelTime) wrapper around $(B core.time.Duration). Note that
12 *        D's duration holds hnsecs count, but reltime precise at least seconds.</li>
13 *   <li>$(B time) - day time without time zone. Corresponding D type - $(B PGTime) wrapper around
14 *        $(B std.datetime.TimeOfDay).</li>
15 *   <li>$(B time with zone) - day time with time zone. Corresponding D type - $(B PGTimeWithZone)
16 *        structure that can be casted to $(B std.datetime.TimeOfDay) and $(B std.datetime.SimpleTimeZone).</li>
17 *   <li>$(B interval) - time duration (modern replacement for $(B reltime)). Corresponding D time - 
18 *        $(B TimeInterval) that handles microsecond, day and month counts.</li>
19 *   <li>$(B tinterval) - interval between two points in time. Consists of two abstime values: begin and end.
20 *        Correponding D type - $(B PGInterval) wrapper around $(B std.datetime.Interval).</li>
21 *   </ul>
22 *
23 *   Copyright: © 2014 DSoftOut
24 *   License: Subject to the terms of the MIT license, as written in the included LICENSE file.
25 *   Authors: NCrashed <ncrashed@gmail.com>
26 */
27 module pgator.db.pq.types.time;
28 
29 import pgator.db.pq.types.oids;
30 import pgator.db.connection;
31 import core.stdc.time;
32 import std.datetime;
33 import std.bitmanip;
34 import vibe.data.bson;
35 import std.math;
36 
37 private void j2date(int jd, out int year, out int month, out int day)
38 {
39     enum POSTGRES_EPOCH_JDATE = 2451545;
40     enum MONTHS_PER_YEAR = 12;
41 
42     jd += POSTGRES_EPOCH_JDATE;
43     
44     uint julian = jd + 32044;
45     uint quad = julian / 146097;
46     uint extra = (julian - quad * 146097) * 4 + 3;
47     julian += 60 + quad * 3 + extra / 146097;
48     quad = julian / 1461;
49     julian -= quad * 1461;
50     int y = julian * 4 / 1461;
51     julian = ((y != 0) ? ((julian + 305) % 365) : ((julian + 306) % 366))
52         + 123;
53     year = (y+ quad * 4) - 4800;
54     quad = julian * 2141 / 65536;
55     day = julian - 7834 * quad / 256;
56     month = (quad + 10) % MONTHS_PER_YEAR + 1;
57 }
58 
59 Date convert(PQType type)(ubyte[] val)
60     if(type == PQType.Date)
61 {
62     assert(val.length == uint.sizeof);
63     uint raw = val.read!uint;
64     int year, month, day;
65     j2date(raw, year, month, day);
66     
67     return Date(year, month, day);
68 }
69 
70 /**
71 *   Wrapper around SysTime to handle libpq abstime.
72 *
73 *   Note: abstime is stored in UTC timezone.
74 */
75 struct PGAbsTime
76 {
77     private SysTime time;
78     alias time this;
79     
80     static PGAbsTime fromBson(Bson bson)
81     {
82         auto val = SysTime.fromISOExtString(bson.get!string);
83         return PGAbsTime(val);
84     }
85     
86     Bson toBson() const
87     {
88         return Bson(time.toISOExtString);
89     }
90 }
91 
92 PGAbsTime convert(PQType type)(ubyte[] val)
93     if(type == PQType.AbsTime)
94 {
95     assert(val.length == 4);
96     return PGAbsTime(SysTime(unixTimeToStdTime(val.read!int), UTC()));
97 }
98 
99 /**
100 *   Wrapper around Duration to handle libpq reltime.
101 *
102 *   Note: reltime can be negative.
103 */
104 struct PGRelTime
105 {
106     private Duration dur;
107     alias dur this;
108     
109     static PGRelTime fromBson(Bson bson)
110     {
111         return PGRelTime(bson.get!long.dur!"seconds");
112     }
113     
114     Bson toBson() const
115     {
116         return Bson(dur.total!"seconds");
117     }
118 }
119 
120 PGRelTime convert(PQType type)(ubyte[] val)
121     if(type == PQType.RelTime)
122 {
123     assert(val.length == 4);
124     return PGRelTime(val.read!int.dur!"seconds");
125 }
126 
127 version(Have_Int64_TimeStamp)
128 {
129     private alias long Timestamp;
130     private alias long TimestampTz;
131     private alias long TimeADT;
132     private alias long TimeOffset;
133     private alias int  fsec_t;      /* fractional seconds (in microseconds) */
134     
135     void TMODULO(ref long t, ref long q, double u)
136     {
137         q = cast(long)(t / u);
138         if (q != 0) t -= q * cast(long)u;
139     }
140 }
141 else
142 {
143     import std.math;
144     
145     private alias double Timestamp;
146     private alias double TimestampTz;
147     private alias double TimeADT;
148     private alias double TimeOffset;
149     private alias double fsec_t;    /* fractional seconds (in seconds) */
150     
151     void TMODULO(T)(ref double t, ref T q, double u)
152         if(is(T == double) || is(T == int))
153     {
154         q = cast(T)((t < 0) ? ceil(t / u) : floor(t / u));
155         if (q != 0) t -= rint(q * u);
156     }
157     
158     double TIMEROUND(double j) 
159     {
160         enum TIME_PREC_INV = 10000000000.0;
161         return rint((cast(double) j) * TIME_PREC_INV) / TIME_PREC_INV;
162     }
163 }
164 
165 private TimeOfDay time2tm(TimeADT time)
166 {
167     version(Have_Int64_TimeStamp)
168     {
169         immutable long USECS_PER_HOUR  = 3600000000;
170         immutable long USECS_PER_MINUTE = 60000000;
171         immutable long USECS_PER_SEC = 1000000;
172         
173         int tm_hour = cast(int)(time / USECS_PER_HOUR);
174         time -= tm_hour * USECS_PER_HOUR;
175         int tm_min = cast(int)(time / USECS_PER_MINUTE);
176         time -= tm_min * USECS_PER_MINUTE;
177         int tm_sec = cast(int)(time / USECS_PER_SEC);
178         time -= tm_sec * USECS_PER_SEC;
179         
180         return TimeOfDay(tm_hour, tm_min, tm_sec);
181     }
182     else
183     {    
184         enum SECS_PER_HOUR = 3600;
185         enum SECS_PER_MINUTE = 60;
186         
187         double      trem;
188         int tm_hour, tm_min, tm_sec;
189     recalc:
190         trem = time;
191         TMODULO(trem, tm_hour, cast(double) SECS_PER_HOUR);
192         TMODULO(trem, tm_min, cast(double) SECS_PER_MINUTE);
193         TMODULO(trem, tm_sec, 1.0);
194         trem = TIMEROUND(trem);
195         /* roundoff may need to propagate to higher-order fields */
196         if (trem >= 1.0)
197         {
198             time = ceil(time);
199             goto recalc;
200         }
201         return TimeOfDay(tm_hour, tm_min, tm_sec);
202     }
203 }
204 
205 /**
206 *   Wrapper around TimeOfDay to allow serializing to bson.
207 */
208 struct PGTime
209 {
210     private TimeOfDay time;
211     alias time this;
212     
213     static PGTime fromBson(Bson bson)
214     {
215         return PGTime(TimeOfDay(bson.hour.get!int, bson.minute.get!int, bson.second.get!int));
216     }
217     
218     Bson toBson() const
219     {
220         Bson[string] map;
221         map["hour"] = time.hour;
222         map["minute"] = time.minute;
223         map["second"] = time.second;
224         return Bson(map);
225     }
226 }
227 
228 PGTime convert(PQType type)(ubyte[] val)
229     if(type == PQType.Time)
230 {
231     assert(val.length == 8);
232     return PGTime(time2tm(val.read!TimeADT));
233 }
234 
235 /**
236 *   Represents PostgreSQL Time with TimeZone.
237 *   Time zone is stored as UTC offset in seconds without DST.
238 */
239 struct PGTimeWithZone
240 {
241     int hour, minute, second, timeZoneOffset;
242     
243     this(TimeOfDay tm, const SimpleTimeZone tz) pure
244     {
245         hour = tm.hour;
246         minute = tm.minute;
247         second = tm.second;
248         
249         static if (__VERSION__ < 2066) {
250         	timeZoneOffset = cast(int)tz.utcOffset.dur!"minutes".total!"seconds";
251     	} else {
252     		timeZoneOffset = cast(int)tz.utcOffset.total!"seconds";
253     	}
254     }
255     
256     T opCast(T)() const if(is(T == TimeOfDay))
257     {
258         return TimeOfDay(hour, minute, second);
259     }
260     
261     T opCast(T)() const if(is(T == immutable SimpleTimeZone))
262     {
263         return new immutable SimpleTimeZone(dur!"seconds"(timeZoneOffset));
264     }
265 }
266 
267 PGTimeWithZone convert(PQType type)(ubyte[] val)
268     if(type == PQType.TimeWithZone)
269 {
270     assert(val.length == 12);
271     return PGTimeWithZone(time2tm(val.read!TimeADT), new immutable SimpleTimeZone(-val.read!int.dur!"seconds"));
272 }
273 
274 /**
275 *   PostgreSQL time interval isn't same with D std.datetime one.
276 *   It is simple Duration.
277 *
278 *   Consists of: microseconds $(B time), $(B day) count and $(B month) count.
279 *   Libpq uses different represantation for $(B time), but i store only 
280 *   in usecs format.
281 */
282 struct TimeInterval
283 {
284     // in microseconds
285     long        time;           /* all time units other than days, months and
286                                  * years */
287     int         day;            /* days, after time for alignment */
288     int         month;          /* months and years, after time for alignment */
289     
290     this(ubyte[] arr)
291     {
292         assert(arr.length == TimeOffset.sizeof + 2*int.sizeof);
293         version(Have_Int64_TimeStamp)
294         {
295             time  = arr.read!long;
296         }
297         else
298         {
299             time  = cast(long)(arr.read!double * 10e6);
300         }
301         
302         day   = arr.read!int;
303         month = arr.read!int;
304     }
305 }
306 
307 TimeInterval convert(PQType type)(ubyte[] val)
308     if(type == PQType.TimeInterval)
309 {
310     return TimeInterval(val);
311 }
312 
313 /**
314 *   Wrapper around std.datetime.Interval to handle [de]serializing
315 *   acceptable for JSON-RPC and still conform with libpq format.
316 *
317 *   Note: libpq representation of abstime slightly different from
318 *   std.datetime, thats why time converted to string could differ
319 *   a lot for PostgreSQL and SysTime (about 9000-15000 seconds nonconstant 
320 *   offset). 
321 */
322 struct PGInterval
323 {
324     private Interval!SysTime interval;
325     alias interval this;
326 
327     this(Interval!SysTime val)
328     {
329     	interval = val;
330     }
331     
332     static PGInterval fromBson(Bson bson)
333     {
334         auto begin = SysTime.fromISOExtString(bson.begin.get!string);
335         auto end   = SysTime.fromISOExtString(bson.end.get!string);
336         return PGInterval(Interval!SysTime(begin, end));
337     }
338     
339     Bson toBson() const
340     {
341         Bson[string] map;
342         map["begin"] = Bson(interval.begin.toISOExtString);
343         map["end"]   = Bson(interval.end.toISOExtString);
344         return Bson(map);
345     }
346 }
347 
348 /// Avoiding linking problems when std.datetime.Interval invariant isn't generated
349 /// by dmd. See at: std.datetime.Interval:18404
350 private debug extern(C) void _D3std8datetime36__T8IntervalTS3std8datetime7SysTimeZ8Interval11__invariantMxFNaZv()
351 {
352 	
353 }
354 
355 PGInterval convert(PQType type)(ubyte[] val)
356     if(type == PQType.Interval)
357 {
358     assert(val.length == 3*int.sizeof);
359     auto state = val.read!int;
360     auto beg = SysTime(unixTimeToStdTime(val.read!int), UTC());
361     auto end = SysTime(unixTimeToStdTime(val.read!int), UTC());
362 
363     return PGInterval(Interval!SysTime(beg, end));
364 }
365  
366 private
367 {
368     struct pg_tm
369     {
370         int         tm_sec;
371         int         tm_min;
372         int         tm_hour;
373         int         tm_mday;
374         int         tm_mon;         /* origin 0, not 1 */
375         int         tm_year;        /* relative to 1900 */
376         int         tm_wday;
377         int         tm_yday;
378         int         tm_isdst;
379         long        tm_gmtoff;
380         string      tm_zone;
381     }
382     
383     alias long pg_time_t;
384     struct pg_tz;
385     
386     immutable ulong SECS_PER_DAY = 86400;
387     immutable ulong POSTGRES_EPOCH_JDATE = 2451545;
388     immutable ulong UNIX_EPOCH_JDATE     = 2440588;
389     
390     immutable ulong USECS_PER_DAY    = 86_400_000_000;
391     immutable ulong USECS_PER_HOUR   = 3_600_000_000;
392     immutable ulong USECS_PER_MINUTE = 60_000_000;
393     immutable ulong USECS_PER_SEC    = 1_000_000;
394     
395     immutable ulong SECS_PER_HOUR   = 3600;
396     immutable ulong SECS_PER_MINUTE = 60;
397     
398     /*
399      *  Round off to MAX_TIMESTAMP_PRECISION decimal places.
400      *  Note: this is also used for rounding off intervals.
401      */
402     enum TS_PREC_INV = 1000000.0;
403     fsec_t TSROUND(fsec_t j)
404     {
405         return cast(fsec_t)(rint((cast(double) (j)) * TS_PREC_INV) / TS_PREC_INV);
406     }
407 }
408 
409  /*
410  * timestamp2tm() - Convert timestamp data type to POSIX time structure.
411  *
412  * Note that year is _not_ 1900-based, but is an explicit full value.
413  * Also, month is one-based, _not_ zero-based.
414  * Returns:
415  *   0 on success
416  *  -1 on out of range
417  *
418  * If attimezone is null, the global timezone (including possibly brute forced
419  * timezone) will be used.
420  */
421 private int timestamp2tm(Timestamp dt, out pg_tm tm, out fsec_t fsec)
422 {
423     Timestamp   date;
424     Timestamp   time;
425     pg_time_t   utime;
426 
427     version(Have_Int64_TimeStamp)
428     {
429         time = dt;
430         TMODULO(time, date, USECS_PER_DAY);
431 
432         if (time < 0)
433         {
434             time += USECS_PER_DAY;
435             date -= 1;
436         }       
437     
438         j2date(cast(int) date, tm.tm_year, tm.tm_mon, tm.tm_mday);
439         dt2time(time, tm.tm_hour, tm.tm_min, tm.tm_sec, fsec);
440     } else
441     {
442         time = dt;
443         TMODULO(time, date, cast(double) SECS_PER_DAY);
444     
445         if (time < 0)
446         {
447             time += SECS_PER_DAY;
448             date -= 1;
449         }    
450          
451     recalc_d:
452         j2date(cast(int) date, tm.tm_year, tm.tm_mon, tm.tm_mday);
453     recalc_t:
454         dt2time(time, tm.tm_hour, tm.tm_min, tm.tm_sec, fsec);
455     
456         fsec = TSROUND(fsec);
457         /* roundoff may need to propagate to higher-order fields */
458         if (fsec >= 1.0)
459         {
460             time = cast(Timestamp)ceil(time);
461             if (time >= cast(double) SECS_PER_DAY)
462             {
463                 time = 0;
464                 date += 1;
465                 goto recalc_d;
466             }
467             goto recalc_t;
468         }
469     }
470 
471     return 0;
472 }
473 
474 private void dt2time(Timestamp jd, out int hour, out int min, out int sec, out fsec_t fsec)
475 {
476     TimeOffset  time;
477 
478     time = jd;
479     version(Have_Int64_TimeStamp)
480     {
481         hour = cast(int)(time / USECS_PER_HOUR);
482         time -= hour * USECS_PER_HOUR;
483         min = cast(int)(time / USECS_PER_MINUTE);
484         time -= min * USECS_PER_MINUTE;
485         sec = cast(int)(time / USECS_PER_SEC);
486         fsec = cast(int)(time - sec*USECS_PER_SEC);
487     } else
488     {
489         hour = cast(int)(time / SECS_PER_HOUR);
490         time -= hour * SECS_PER_HOUR;
491         min = cast(int)(time / SECS_PER_MINUTE);
492         time -= min * SECS_PER_MINUTE;
493         sec = cast(int)time;
494         fsec = cast(int)(time - sec);
495     }
496 } 
497 
498 /**
499 *   Wrapper around std.datetime.SysTime to handle [de]serializing of libpq
500 *   timestamps (without time zone).
501 *
502 *   Note: libpq has two compile configuration with HAS_INT64_TIMESTAMP and
503 *   without (double timestamp format). The pgator should be compiled with
504 *   conform flag to operate properly. 
505 */
506 struct PGTimeStamp
507 {
508     private SysTime time;
509     alias time this;
510     
511     this(SysTime time)
512     {
513         this.time = time;
514     }
515     
516     this(pg_tm tm, fsec_t ts)
517     {
518         time = SysTime(Date(tm.tm_year, tm.tm_mon, tm.tm_mday), UTC());
519         time += (tm.tm_hour % 24).dur!"hours";
520         time += (tm.tm_min % 60).dur!"minutes";
521         time += (tm.tm_sec  % 60).dur!"seconds";
522         version(Have_Int64_TimeStamp)
523         {
524             time += ts.dur!"usecs";
525         } else
526         {
527             time += (cast(long)(ts*10e6)).dur!"usecs";
528         }
529     }
530     
531     static PGTimeStamp fromBson(Bson bson)
532     {
533         auto val = SysTime.fromISOExtString(bson.get!string);
534         return PGTimeStamp(val);
535     }
536     
537     Bson toBson() const
538     {
539         return Bson(time.toISOExtString);
540     }
541 }
542 
543 PGTimeStamp convert(PQType type)(ubyte[] val)
544     if(type == PQType.TimeStamp)
545 {
546     auto raw = val.read!long;
547     
548     version(Have_Int64_TimeStamp)
549     {
550         if(raw >= time_t.max)
551         {
552             return PGTimeStamp(SysTime.max);
553         }
554         if(raw <= time_t.min)
555         {
556             return PGTimeStamp(SysTime.min);
557         }
558     }
559     
560     pg_tm tm;
561     fsec_t ts;
562     
563     if(timestamp2tm(raw, tm, ts) < 0)
564         throw new Exception("Timestamp is out of range!");
565 
566     return PGTimeStamp(tm, ts);
567 }
568 
569 /**
570 *   Wrapper around std.datetime.SysTime to handle [de]serializing of libpq
571 *   time stamps with time zone.
572 *
573 *   Timezone is acquired from PQparameterStatus call for TimeZone parameter.
574 *   Database server doesn't send any info about time zone to client, the
575 *   time zone is important only while showing time to an user.
576 *
577 *   Note: libpq has two compile configuration with HAS_INT64_TIMESTAMP and
578 *   without (double time stamp format). The pgator should be compiled with
579 *   conform flag to operate properly. 
580 */
581 struct PGTimeStampWithZone
582 {
583     private SysTime time;
584     alias time this;
585     
586     this(SysTime time)
587     {
588         this.time = time;
589     }
590     
591     this(pg_tm tm, fsec_t ts, immutable TimeZone zone)
592     {    
593         time = SysTime(Date(tm.tm_year, tm.tm_mon, tm.tm_mday), UTC());
594         time.timezone = zone;
595         time += tm.tm_hour.dur!"hours";
596         time += tm.tm_min.dur!"minutes";
597         time += tm.tm_sec.dur!"seconds";
598         version(Have_Int64_TimeStamp)
599         {
600             time += ts.dur!"usecs";
601         } else
602         {
603             time += (cast(long)(ts*10e6)).dur!"usecs";
604         }
605     }
606     
607     static PGTimeStampWithZone fromBson(Bson bson)
608     {
609         auto val = SysTime.fromISOExtString(bson.get!string);
610         return PGTimeStampWithZone(val);
611     }
612     
613     Bson toBson() const
614     {
615         return Bson(time.toISOExtString);
616     }
617 }
618 
619 PGTimeStampWithZone convert(PQType type)(ubyte[] val, shared IConnection conn)
620     if(type == PQType.TimeStampWithZone)
621 {
622     auto raw = val.read!long;
623     
624     version(Have_Int64_TimeStamp)
625     {
626         if(raw >= time_t.max)
627         {
628             return PGTimeStampWithZone(SysTime.max);
629         }
630         if(raw <= time_t.min)
631         {
632             return PGTimeStampWithZone(SysTime.min);
633         }
634     }
635     
636     pg_tm tm;
637     fsec_t ts;
638     
639     if(timestamp2tm(raw, tm, ts) < 0)
640         throw new Exception("Timestamp is out of range!");
641 
642     return PGTimeStampWithZone(tm, ts, conn.timeZone);
643 }
644 
645 
646 version(IntegrationTest2)
647 {
648     import pgator.db.pq.types.test;
649     import pgator.db.pool;
650     import std.random;
651     import std.algorithm;
652     import std.encoding;
653     import dlogg.log;
654     import dlogg.buffered;
655     
656     void test(PQType type)(shared ILogger strictLogger, shared IConnectionPool pool)
657         if(type == PQType.Date)
658     {
659         strictLogger.logInfo("Testing Date...");
660         auto dformat = pool.dateFormat;
661         auto logger = new shared BufferedLogger(strictLogger);
662         scope(failure) logger.minOutputLevel = LoggingLevel.Notice;
663         scope(exit) logger.finalize;
664         
665         assert(queryValue(logger, pool, "'1999-01-08'::date").deserializeBson!Date.toISOExtString == "1999-01-08");
666         assert(queryValue(logger, pool, "'January 8, 1999'::date").deserializeBson!Date.toISOExtString == "1999-01-08");
667         assert(queryValue(logger, pool, "'1999-Jan-08'::date").deserializeBson!Date.toISOExtString == "1999-01-08");
668         assert(queryValue(logger, pool, "'Jan-08-1999'::date").deserializeBson!Date.toISOExtString == "1999-01-08");
669         assert(queryValue(logger, pool, "'08-Jan-1999'::date").deserializeBson!Date.toISOExtString == "1999-01-08");
670         assert(queryValue(logger, pool, "'19990108'::date").deserializeBson!Date.toISOExtString == "1999-01-08");
671         assert(queryValue(logger, pool, "'990108'::date").deserializeBson!Date.toISOExtString == "1999-01-08");
672         assert(queryValue(logger, pool, "'1999.008'::date").deserializeBson!Date.toISOExtString == "1999-01-08");
673         assert(queryValue(logger, pool, "'J2451187'::date").deserializeBson!Date.toISOExtString == "1999-01-08");
674         assert(queryValue(logger, pool, "'January 8, 99 BC'::date").deserializeBson!Date.toISOExtString == "-0098-01-08");
675            
676         if(dformat.orderFormat == DateFormat.OrderFormat.MDY)
677         {
678             assert(queryValue(logger, pool, "'1/8/1999'::date").deserializeBson!Date.toISOExtString == "1999-01-08");
679             assert(queryValue(logger, pool, "'1/18/1999'::date").deserializeBson!Date.toISOExtString == "1999-01-18");
680             assert(queryValue(logger, pool, "'01/02/03'::date").deserializeBson!Date.toISOExtString == "2003-01-02");
681             assert(queryValue(logger, pool, "'08-Jan-99'::date").deserializeBson!Date.toISOExtString == "1999-01-08");
682             assert(queryValue(logger, pool, "'Jan-08-99'::date").deserializeBson!Date.toISOExtString == "1999-01-08");
683         } 
684         else if(dformat.orderFormat == DateFormat.OrderFormat.DMY)
685         {
686             assert(queryValue(logger, pool, "'1/8/1999'::date").deserializeBson!Date.toISOExtString == "1999-08-01");
687             assert(queryValue(logger, pool, "'01/02/03'::date").deserializeBson!Date.toISOExtString == "2003-02-01");
688             assert(queryValue(logger, pool, "'08-Jan-99'::date").deserializeBson!Date.toISOExtString == "1999-01-08");
689             assert(queryValue(logger, pool, "'Jan-08-99'::date").deserializeBson!Date.toISOExtString == "1999-01-08");
690         }
691         else if(dformat.orderFormat == DateFormat.OrderFormat.YMD)
692         {
693             assert(queryValue(logger, pool, "'01/02/03'::date").deserializeBson!Date.toISOExtString == "2001-02-03");
694             assert(queryValue(logger, pool, "'99-Jan-08'::date").deserializeBson!Date.toISOExtString == "1999-01-08");
695         }
696 
697     }
698     
699     void test(PQType type)(shared ILogger strictLogger, shared IConnectionPool pool)
700         if(type == PQType.AbsTime)
701     {
702         strictLogger.logInfo("Testing AbsTime...");
703         
704         auto logger = new shared BufferedLogger(strictLogger);
705         scope(failure) logger.minOutputLevel = LoggingLevel.Notice;
706         scope(exit) logger.finalize;
707         
708         auto res = queryValue(logger, pool, "'Dec 20 20:45:53 1986 GMT'::abstime").deserializeBson!PGAbsTime;
709         assert(res.time == SysTime.fromSimpleString("1986-Dec-20 20:45:53Z"));
710         
711         res = queryValue(logger, pool, "'Mar 8 03:14:04 2014 GMT'::abstime").deserializeBson!PGAbsTime;
712         assert(res.time == SysTime.fromSimpleString("2014-Mar-08 03:14:04Z"));
713         
714         res = queryValue(logger, pool, "'Dec 20 20:45:53 1986 +3'::abstime").deserializeBson!PGAbsTime;
715         assert(res.time == SysTime.fromSimpleString("1986-Dec-20 20:45:53+03"));
716         
717         res = queryValue(logger, pool, "'Mar 8 03:14:04 2014 +3'::abstime").deserializeBson!PGAbsTime;
718         assert(res.time == SysTime.fromSimpleString("2014-Mar-08 03:14:04+03"));
719         
720     }
721      
722     void test(PQType type)(shared ILogger strictLogger, shared IConnectionPool pool)
723         if(type == PQType.RelTime)
724     {
725         strictLogger.logInfo("Testing RelTime...");
726         
727         auto logger = new shared BufferedLogger(strictLogger);
728         scope(failure) logger.minOutputLevel = LoggingLevel.Notice;
729         scope(exit) logger.finalize;
730         
731         auto res = queryValue(logger, pool, "'2 week 3 day 4 hour 5 minute 6 second'::reltime").deserializeBson!PGRelTime;
732         assert(res == 6.dur!"seconds" + 5.dur!"minutes" + 4.dur!"hours" + 3.dur!"days" + 2.dur!"weeks");
733         
734         res = queryValue(logger, pool, "'2 week 3 day 4 hour 5 minute 6 second ago'::reltime").deserializeBson!PGRelTime;
735         assert(res == (-6).dur!"seconds" + (-5).dur!"minutes" + (-4).dur!"hours" + (-3).dur!"days" + (-2).dur!"weeks");
736     }
737     
738     void test(PQType type)(shared ILogger strictLogger, shared IConnectionPool pool)
739         if(type == PQType.Time)
740     {
741         strictLogger.logInfo("Testing Time...");
742         scope(failure) 
743         {
744             version(Have_Int64_TimeStamp) string s = "with Have_Int64_TimeStamp";
745             else string s = "without Have_Int64_TimeStamp";
746             
747             strictLogger.logInfo("============================================");
748             strictLogger.logInfo(text("Server timestamp format is: ", pool.timestampFormat));
749             strictLogger.logInfo(text("Application was compiled ", s, ". Try to switch the compilation flag."));
750             strictLogger.logInfo("============================================");
751         }
752 
753         auto logger = new shared BufferedLogger(strictLogger);
754         scope(failure) logger.minOutputLevel = LoggingLevel.Notice;
755         scope(exit) logger.finalize;
756         
757         assert((cast(TimeOfDay)queryValue(logger, pool, "'04:05:06.789'::time").deserializeBson!PGTime).toISOExtString == "04:05:06");
758         assert((cast(TimeOfDay)queryValue(logger, pool, "'04:05:06'::time").deserializeBson!PGTime).toISOExtString == "04:05:06");
759         assert((cast(TimeOfDay)queryValue(logger, pool, "'04:05'::time").deserializeBson!PGTime).toISOExtString == "04:05:00");
760         assert((cast(TimeOfDay)queryValue(logger, pool, "'040506'::time").deserializeBson!PGTime).toISOExtString == "04:05:06");
761         assert((cast(TimeOfDay)queryValue(logger, pool, "'04:05 AM'::time").deserializeBson!PGTime).toISOExtString == "04:05:00");
762         assert((cast(TimeOfDay)queryValue(logger, pool, "'04:05 PM'::time").deserializeBson!PGTime).toISOExtString == "16:05:00");
763     }
764     
765     void test(PQType type)(shared ILogger strictLogger, shared IConnectionPool pool)
766         if(type == PQType.TimeWithZone)
767     {
768         strictLogger.logInfo("Testing TimeWithZone...");
769         scope(failure) 
770         {
771             version(Have_Int64_TimeStamp) string s = "with Have_Int64_TimeStamp";
772             else string s = "without Have_Int64_TimeStamp";
773             
774             strictLogger.logInfo("============================================");
775             strictLogger.logInfo(text("Server timestamp format is: ", pool.timestampFormat));
776             strictLogger.logInfo(text("Application was compiled ", s, ". Try to switch the compilation flag."));
777             strictLogger.logInfo("============================================");
778         }
779         
780         auto logger = new shared BufferedLogger(strictLogger);
781         scope(failure) logger.minOutputLevel = LoggingLevel.Notice;
782         scope(exit) logger.finalize;
783         
784         static if (__VERSION__ < 2066) 
785         {
786             auto res = queryValue(logger, pool, "'04:05:06.789-8'::time with time zone").deserializeBson!PGTimeWithZone;
787             assert((cast(TimeOfDay)res).toISOExtString == "04:05:06" && (cast(immutable SimpleTimeZone)res).utcOffset.dur!"minutes".total!"hours" == -8);
788             res = queryValue(logger, pool, "'04:05:06-08:00'::time with time zone").deserializeBson!PGTimeWithZone;
789             assert((cast(TimeOfDay)res).toISOExtString == "04:05:06" && (cast(immutable SimpleTimeZone)res).utcOffset.dur!"minutes".total!"hours" == -8);
790             res = queryValue(logger, pool, "'04:05-08:00'::time with time zone").deserializeBson!PGTimeWithZone;
791             assert((cast(TimeOfDay)res).toISOExtString == "04:05:00" && (cast(immutable SimpleTimeZone)res).utcOffset.dur!"minutes".total!"hours" == -8);
792             res = queryValue(logger, pool, "'040506-08'::time with time zone").deserializeBson!PGTimeWithZone;
793             assert((cast(TimeOfDay)res).toISOExtString == "04:05:06" && (cast(immutable SimpleTimeZone)res).utcOffset.dur!"minutes".total!"hours" == -8);
794             res = queryValue(logger, pool, "'04:05:06 PST'::time with time zone").deserializeBson!PGTimeWithZone;
795             assert((cast(TimeOfDay)res).toISOExtString == "04:05:06" && (cast(immutable SimpleTimeZone)res).utcOffset.dur!"minutes".total!"hours" == -8);
796             res = queryValue(logger, pool, "'2003-04-12 04:05:06 America/New_York'::time with time zone").deserializeBson!PGTimeWithZone;
797             assert((cast(TimeOfDay)res).toISOExtString == "04:05:06" && (cast(immutable SimpleTimeZone)res).utcOffset.dur!"minutes".total!"hours" == -4);
798         } else
799         {
800             auto res = queryValue(logger, pool, "'04:05:06.789-8'::time with time zone").deserializeBson!PGTimeWithZone;
801             assert((cast(TimeOfDay)res).toISOExtString == "04:05:06" && (cast(immutable SimpleTimeZone)res).utcOffset.total!"hours" == -8);
802             res = queryValue(logger, pool, "'04:05:06-08:00'::time with time zone").deserializeBson!PGTimeWithZone;
803             assert((cast(TimeOfDay)res).toISOExtString == "04:05:06" && (cast(immutable SimpleTimeZone)res).utcOffset.total!"hours" == -8);
804             res = queryValue(logger, pool, "'04:05-08:00'::time with time zone").deserializeBson!PGTimeWithZone;
805             assert((cast(TimeOfDay)res).toISOExtString == "04:05:00" && (cast(immutable SimpleTimeZone)res).utcOffset.total!"hours" == -8);
806             res = queryValue(logger, pool, "'040506-08'::time with time zone").deserializeBson!PGTimeWithZone;
807             assert((cast(TimeOfDay)res).toISOExtString == "04:05:06" && (cast(immutable SimpleTimeZone)res).utcOffset.total!"hours" == -8);
808             res = queryValue(logger, pool, "'04:05:06 PST'::time with time zone").deserializeBson!PGTimeWithZone;
809             assert((cast(TimeOfDay)res).toISOExtString == "04:05:06" && (cast(immutable SimpleTimeZone)res).utcOffset.total!"hours" == -8);
810             res = queryValue(logger, pool, "'2003-04-12 04:05:06 America/New_York'::time with time zone").deserializeBson!PGTimeWithZone;
811             assert((cast(TimeOfDay)res).toISOExtString == "04:05:06" && (cast(immutable SimpleTimeZone)res).utcOffset.total!"hours" == -4);
812         }
813     }
814     
815     void test(PQType type)(shared ILogger strictLogger, shared IConnectionPool pool)
816         if(type == PQType.Interval)
817     {
818         strictLogger.logInfo("Testing tinterval...");
819                 
820         auto logger = new shared BufferedLogger(strictLogger);
821         scope(failure) logger.minOutputLevel = LoggingLevel.Notice;
822         scope(exit) logger.finalize;
823         
824         auto res = queryValue(logger, pool, "'[\"Dec 20 20:45:53 1986 GMT\" \"Mar 8 03:14:04 2014 GMT\"]'::tinterval").deserializeBson!PGInterval;
825         assert(res.begin == SysTime.fromSimpleString("1986-Dec-20 20:45:53Z"));
826         assert(res.end   == SysTime.fromSimpleString("2014-Mar-08 03:14:04Z"));
827         
828         res = queryValue(logger, pool, "'[\"Dec 20 20:45:53 1986 +3\" \"Mar 8 03:14:04 2014 +3\"]'::tinterval").deserializeBson!PGInterval;
829         assert(res.begin == SysTime.fromSimpleString("1986-Dec-20 20:45:53+03"));
830         assert(res.end   == SysTime.fromSimpleString("2014-Mar-08 03:14:04+03"));
831 
832     }
833     
834     void test(PQType type)(shared ILogger strictLogger, shared IConnectionPool pool)
835         if(type == PQType.TimeInterval)
836     {
837         strictLogger.logInfo("Testing TimeInterval...");
838         scope(failure) 
839         {
840             version(Have_Int64_TimeStamp) string s = "with Have_Int64_TimeStamp";
841             else string s = "without Have_Int64_TimeStamp";
842             
843             strictLogger.logInfo("============================================");
844             strictLogger.logInfo(text("Server timestamp format is: ", pool.timestampFormat));
845             strictLogger.logInfo(text("Application was compiled ", s, ". Try to switch the compilation flag."));
846             strictLogger.logInfo("============================================");
847         }
848         
849         auto logger = new shared BufferedLogger(strictLogger);
850         scope(failure) logger.minOutputLevel = LoggingLevel.Notice;
851         scope(exit) logger.finalize;
852         
853         auto res = queryValue(logger, pool, "'1-2'::interval").deserializeBson!TimeInterval;
854         assert(res.time == 0 && res.day == 0 && res.month == 14);
855         
856         res = queryValue(logger, pool, "'3 4:05:06'::interval").deserializeBson!TimeInterval;
857         assert(res.time.dur!"usecs" == 4.dur!"hours" + 5.dur!"minutes" + 6.dur!"seconds" && res.day == 3 && res.month == 0);
858         
859         res = queryValue(logger, pool, "'1 year 2 months 3 days 4 hours 5 minutes 6 seconds'::interval").deserializeBson!TimeInterval;
860         assert(res.time.dur!"usecs" == 4.dur!"hours" + 5.dur!"minutes" + 6.dur!"seconds" && res.day == 3 && res.month == 14);
861         
862         res = queryValue(logger, pool, "'P1Y2M3DT4H5M6S'::interval").deserializeBson!TimeInterval;
863         assert(res.time.dur!"usecs" == 4.dur!"hours" + 5.dur!"minutes" + 6.dur!"seconds" && res.day == 3 && res.month == 14);
864         
865         res = queryValue(logger, pool, "'P0001-02-03T04:05:06'::interval").deserializeBson!TimeInterval;
866         assert(res.time.dur!"usecs" == 4.dur!"hours" + 5.dur!"minutes" + 6.dur!"seconds" && res.day == 3 && res.month == 14);
867     }
868     
869     void test(PQType type)(shared ILogger strictLogger, shared IConnectionPool pool)
870         if(type == PQType.TimeStamp)
871     {
872         strictLogger.logInfo("Testing TimeStamp...");
873         scope(failure) 
874         {
875             version(Have_Int64_TimeStamp) string s = "with Have_Int64_TimeStamp";
876             else string s = "without Have_Int64_TimeStamp";
877             
878             strictLogger.logInfo("============================================");
879             strictLogger.logInfo(text("Server timestamp format is: ", pool.timestampFormat));
880             strictLogger.logInfo(text("Application was compiled ", s, ". Try to switch the compilation flag."));
881             strictLogger.logInfo("============================================");
882         }
883         
884         auto logger = new shared BufferedLogger(strictLogger);
885         scope(failure) logger.minOutputLevel = LoggingLevel.Notice;
886         scope(exit) logger.finalize;
887         
888         auto res = queryValue(logger, pool, "TIMESTAMP '2004-10-19 10:23:54'").deserializeBson!PGTimeStamp;
889         assert(res.time == SysTime.fromSimpleString("2004-Oct-19 10:23:54Z"));
890         
891         res = queryValue(logger, pool, "TIMESTAMP '1999-01-08 04:05:06'").deserializeBson!PGTimeStamp;
892         assert(res.time == SysTime.fromSimpleString("1999-Jan-08 04:05:06Z"));
893         
894         res = queryValue(logger, pool, "TIMESTAMP 'January 8 04:05:06 1999 PST'").deserializeBson!PGTimeStamp;
895         assert(res.time == SysTime.fromSimpleString("1999-Jan-08 04:05:06Z"));
896         
897         res = queryValue(logger, pool, "TIMESTAMP 'epoch'").deserializeBson!PGTimeStamp;
898         assert(res.time == SysTime.fromSimpleString("1970-Jan-01 00:00:00Z"));
899         
900         res = queryValue(logger, pool, "TIMESTAMP 'infinity'").deserializeBson!PGTimeStamp;
901         assert(res.time == SysTime.max);
902         
903         res = queryValue(logger, pool, "TIMESTAMP '-infinity'").deserializeBson!PGTimeStamp;
904         assert(res.time == SysTime.min);
905     }
906      
907     void test(PQType type)(shared ILogger strictLogger, shared IConnectionPool pool)
908         if(type == PQType.TimeStampWithZone)
909     {
910         strictLogger.logInfo("Testing TimeStampWithZone...");
911         scope(failure) 
912         {
913             version(Have_Int64_TimeStamp) string s = "with Have_Int64_TimeStamp";
914             else string s = "without Have_Int64_TimeStamp";
915             
916             strictLogger.logInfo("============================================");
917             strictLogger.logInfo(text("Server timestamp format is: ", pool.timestampFormat));
918             strictLogger.logInfo(text("Application was compiled ", s, ". Try to switch the compilation flag."));
919             strictLogger.logInfo("============================================");
920         }
921         
922         auto logger = new shared BufferedLogger(strictLogger);
923         scope(failure) logger.minOutputLevel = LoggingLevel.Notice;
924         scope(exit) logger.finalize;
925         
926         auto res = queryValue(logger, pool, "TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'").deserializeBson!PGTimeStampWithZone;
927         assert(res.time == SysTime.fromSimpleString("2004-Oct-19 10:23:54+02"));
928         
929         res = queryValue(logger, pool, "TIMESTAMP WITH TIME ZONE '1999-01-08 04:05:06-04'").deserializeBson!PGTimeStampWithZone;
930         assert(res.time == SysTime.fromSimpleString("1999-Jan-08 04:05:06-04"));
931         
932         res = queryValue(logger, pool, "TIMESTAMP WITH TIME ZONE 'January 8 04:05:06 1999 -8:00'").deserializeBson!PGTimeStampWithZone;
933         assert(res.time == SysTime.fromSimpleString("1999-Jan-08 04:05:06-08"));
934         
935         res = queryValue(logger, pool, "TIMESTAMP WITH TIME ZONE 'epoch'").deserializeBson!PGTimeStampWithZone;
936         assert(res.time == SysTime.fromSimpleString("1970-Jan-01 00:00:00Z"));
937         
938         res = queryValue(logger, pool, "TIMESTAMP WITH TIME ZONE 'infinity'").deserializeBson!PGTimeStampWithZone;
939         assert(res.time == SysTime.max);
940         
941         res = queryValue(logger, pool, "TIMESTAMP WITH TIME ZONE '-infinity'").deserializeBson!PGTimeStampWithZone;
942         assert(res.time == SysTime.min);
943         
944         res = queryValue(logger, pool, "TIMESTAMP WITH TIME ZONE '2014-11-20 15:47:25+07'").deserializeBson!PGTimeStampWithZone;
945         assert(res.time == SysTime.fromSimpleString("2014-Nov-20 15:47:25+07"));
946     }
947 }