denizzzka / dpq2
1
/**
2
*   PostgreSQL time types binary format.
3
*
4
*   Copyright: © 2014 DSoftOut
5
*   Authors: NCrashed <ncrashed@gmail.com>
6
*/
7
module dpq2.conv.time;
8

9
@safe:
10

11
import dpq2.result;
12
import dpq2.oids: OidType;
13
import dpq2.value: throwTypeComplaint;
14

15
import core.time;
16
import std.datetime.date : Date, DateTime, TimeOfDay;
17
import std.datetime.systime: SysTime;
18
import std.datetime.timezone: LocalTime, TimeZone, UTC;
19
import std.bitmanip: bigEndianToNative, nativeToBigEndian;
20
import std.math;
21
import std.conv: to;
22

23
/++
24
    Returns value timestamp with time zone as SysTime
25

26
    Note that SysTime has a precision in hnsecs and PG TimeStamp in usecs.
27
    It means that PG value will have 10 times lower precision.
28
    And as both types are using long for internal storage it also means that PG TimeStamp can store greater range of values than SysTime.
29

30
    Because of these differences, it can happen that database value will not fit to the SysTime range of values.
31
+/
32
SysTime binaryValueAs(T)(in Value v) @trusted
33
if( is( T == SysTime ) )
34
{
35 1
    if(!(v.oidType == OidType.TimeStampWithZone))
36 0
        throwTypeComplaint(v.oidType, "timestamp with time zone", __FILE__, __LINE__);
37

38 1
    if(!(v.data.length == long.sizeof))
39 0
        throw new ValueConvException(ConvExceptionType.SIZE_MISMATCH,
40
            "Value length isn't equal to Postgres timestamp with time zone type", __FILE__, __LINE__);
41

42 1
    auto t = rawTimeStamp2nativeTime!TimeStampUTC(bigEndianToNative!long(v.data.ptr[0..long.sizeof]));
43 1
    return SysTime(t.dateTime, t.fracSec, UTC());
44
}
45

46
pure:
47

48
/// Returns value data as native Date
49
Date binaryValueAs(T)(in Value v) @trusted
50
if( is( T == Date ) )
51
{
52 1
    if(!(v.oidType == OidType.Date))
53 0
        throwTypeComplaint(v.oidType, "Date", __FILE__, __LINE__);
54

55 1
    if(!(v.data.length == uint.sizeof))
56 0
        throw new ValueConvException(ConvExceptionType.SIZE_MISMATCH,
57
            "Value length isn't equal to Postgres date type", __FILE__, __LINE__);
58

59 1
    int jd = bigEndianToNative!uint(v.data.ptr[0..uint.sizeof]);
60 1
    int year, month, day;
61 1
    j2date(jd, year, month, day);
62

63
    // TODO: support PG Date like TTimeStamp manner and remove this check
64 1
    if(year > short.max)
65 0
        throw new ValueConvException(ConvExceptionType.DATE_VALUE_OVERFLOW,
66
            "Year "~year.to!string~" is bigger than supported by std.datetime.Date", __FILE__, __LINE__);
67

68 1
    return Date(year, month, day);
69
}
70

71
/// Returns value time without time zone as native TimeOfDay
72
TimeOfDay binaryValueAs(T)(in Value v) @trusted
73
if( is( T == TimeOfDay ) )
74
{
75 1
    if(!(v.oidType == OidType.Time))
76 0
        throwTypeComplaint(v.oidType, "time without time zone", __FILE__, __LINE__);
77

78 1
    if(!(v.data.length == TimeADT.sizeof))
79 0
        throw new ValueConvException(ConvExceptionType.SIZE_MISMATCH,
80
            "Value length isn't equal to Postgres time without time zone type", __FILE__, __LINE__);
81

82 1
    return time2tm(bigEndianToNative!TimeADT(v.data.ptr[0..TimeADT.sizeof]));
83
}
84

85
/// Returns value timestamp without time zone as TimeStamp
86
TimeStamp binaryValueAs(T)(in Value v) @trusted
87
if( is( T == TimeStamp ) )
88
{
89 1
    if(!(v.oidType == OidType.TimeStamp))
90 0
        throwTypeComplaint(v.oidType, "timestamp without time zone", __FILE__, __LINE__);
91

92 1
    if(!(v.data.length == long.sizeof))
93 0
        throw new ValueConvException(ConvExceptionType.SIZE_MISMATCH,
94
            "Value length isn't equal to Postgres timestamp without time zone type", __FILE__, __LINE__);
95

96 1
    return rawTimeStamp2nativeTime!TimeStamp(
97
        bigEndianToNative!long(v.data.ptr[0..long.sizeof])
98
    );
99
}
100

101
/// Returns value timestamp with time zone as TimeStampUTC
102
TimeStampUTC binaryValueAs(T)(in Value v) @trusted
103
if( is( T == TimeStampUTC ) )
104
{
105 0
    if(!(v.oidType == OidType.TimeStampWithZone))
106 0
        throwTypeComplaint(v.oidType, "timestamp with time zone", __FILE__, __LINE__);
107

108 0
    if(!(v.data.length == long.sizeof))
109 0
        throw new ValueConvException(ConvExceptionType.SIZE_MISMATCH,
110
            "Value length isn't equal to Postgres timestamp with time zone type", __FILE__, __LINE__);
111

112 0
    return rawTimeStamp2nativeTime!TimeStampUTC(
113
        bigEndianToNative!long(v.data.ptr[0..long.sizeof])
114
    );
115
}
116

117
/// Returns value timestamp without time zone as DateTime (it drops the fracSecs from the database value)
118
DateTime binaryValueAs(T)(in Value v) @trusted
119
if( is( T == DateTime ) )
120
{
121 1
    return v.binaryValueAs!TimeStamp.dateTime;
122
}
123

124
///
125
enum InfinityState : byte
126
{
127
    NONE = 0, ///
128
    INFINITY_MIN = -1, ///
129
    INFINITY_MAX = 1, ///
130
}
131

132
///
133
struct PgDate
134
{
135
    int year; ///
136
    ubyte month; ///
137
    ubyte day; ///
138

139
    /// '-infinity', earlier than all other dates
140 1
    static PgDate earlier() pure { return PgDate(int.min, 0, 0); }
141

142
    /// 'infinity', later than all other dates
143 1
    static PgDate later() pure { return PgDate(int.max, 0, 0); }
144

145 1
    bool isEarlier() const pure { return year == earlier.year; } /// '-infinity'
146 1
    bool isLater() const pure { return year == later.year; } /// 'infinity'
147
}
148

149
///
150
static toPgDate(Date d) pure
151
{
152 1
    return PgDate(d.year, d.month, d.day);
153
}
154

155
/++
156
    Structure to represent PostgreSQL Timestamp with/without time zone
157
+/
158
struct TTimeStamp(bool isWithTZ)
159
{
160
    /**
161
     * Date and time of TimeStamp
162
     *
163
     * If value is '-infinity' or '+infinity' it will be equal PgDate.min or PgDate.max
164
     */
165
    PgDate date;
166
    TimeOfDay time; ///
167
    Duration fracSec; /// fractional seconds, 1 microsecond resolution
168

169
    ///
170 1
    this(DateTime dt, Duration fractionalSeconds = Duration.zero) pure
171
    {
172 1
        this(dt.date.toPgDate, dt.timeOfDay, fractionalSeconds);
173
    }
174

175
    ///
176 1
    this(PgDate d, TimeOfDay t = TimeOfDay(), Duration fractionalSeconds = Duration.zero) pure
177
    {
178 1
        date = d;
179 1
        time = t;
180 1
        fracSec = fractionalSeconds;
181
    }
182

183
    ///
184
    void throwIfNotFitsToDate() const
185
    {
186 1
        if(date.year > short.max)
187 0
            throw new ValueConvException(ConvExceptionType.DATE_VALUE_OVERFLOW,
188
                "Year "~date.year.to!string~" is bigger than supported by std.datetime", __FILE__, __LINE__);
189
    }
190

191
    ///
192
    DateTime dateTime() const pure
193
    {
194 1
        if(infinity != InfinityState.NONE)
195 1
            throw new ValueConvException(ConvExceptionType.DATE_VALUE_OVERFLOW,
196
                "TTimeStamp value is "~infinity.to!string, __FILE__, __LINE__);
197

198 1
        throwIfNotFitsToDate();
199

200 1
        return DateTime(Date(date.year, date.month, date.day), time);
201
    }
202

203
    invariant()
204
    {
205 1
        assert(fracSec < 1.seconds, "fracSec can't be more than 1 second but contains "~fracSec.to!string);
206 1
        assert(fracSec >= Duration.zero, "fracSec is negative: "~fracSec.to!string);
207 1
        assert(fracSec % 1.usecs == 0.hnsecs, "fracSec have 1 microsecond resolution but contains "~fracSec.to!string);
208
    }
209

210 1
    bool isEarlier() const pure { return date.isEarlier; } /// '-infinity'
211 1
    bool isLater() const pure { return date.isLater; } /// 'infinity'
212

213
    /// Returns infinity state
214
    InfinityState infinity() const pure
215
    {
216
        with(InfinityState)
217
        {
218 1
            if(isEarlier) return INFINITY_MIN;
219 1
            if(isLater) return INFINITY_MAX;
220

221 1
            return NONE;
222
        }
223
    }
224

225
    unittest
226
    {
227 1
        assert(TTimeStamp.min == TTimeStamp.min);
228 1
        assert(TTimeStamp.max == TTimeStamp.max);
229 1
        assert(TTimeStamp.min != TTimeStamp.max);
230

231 1
        assert(TTimeStamp.earlier != TTimeStamp.later);
232 1
        assert(TTimeStamp.min != TTimeStamp.earlier);
233 1
        assert(TTimeStamp.max != TTimeStamp.later);
234

235 1
        assert(TTimeStamp.min.infinity == InfinityState.NONE);
236 1
        assert(TTimeStamp.max.infinity == InfinityState.NONE);
237 1
        assert(TTimeStamp.earlier.infinity == InfinityState.INFINITY_MIN);
238 1
        assert(TTimeStamp.later.infinity == InfinityState.INFINITY_MAX);
239
    }
240

241
    /// Returns the TimeStamp farthest in the past which is representable by TimeStamp.
242
    static immutable(TTimeStamp) min()
243
    {
244
        /*
245
        Postgres low value is 4713 BC but here is used -4712 because
246
        "Date uses the Proleptic Gregorian Calendar, so it assumes the
247
        Gregorian leap year calculations for its entire length. As per
248
        ISO 8601, it treats 1 B.C. as year 0, i.e. 1 B.C. is 0, 2 B.C.
249
        is -1, etc." (Phobos docs). But Postgres isn't uses ISO 8601
250
        for date calculation.
251
        */
252 1
        return TTimeStamp(PgDate(-4712, 1, 1), TimeOfDay.min, Duration.zero);
253
    }
254

255
    /// Returns the TimeStamp farthest in the future which is representable by TimeStamp.
256
    static immutable(TTimeStamp) max()
257
    {
258
        enum maxFract = 1.seconds - 1.usecs;
259

260 1
        return TTimeStamp(PgDate(294276, 12, 31), TimeOfDay(23, 59, 59), maxFract);
261
    }
262

263
    /// '-infinity', earlier than all other time stamps
264 1
    static immutable(TTimeStamp) earlier() pure { return TTimeStamp(PgDate.earlier); }
265

266
    /// 'infinity', later than all other time stamps
267 1
    static immutable(TTimeStamp) later() pure { return TTimeStamp(PgDate.later); }
268

269
    ///
270
    string toString() const
271
    {
272
        import std.format;
273

274 0
        return format("%04d-%02d-%02d %s %s", date.year, date.month, date.day, time, fracSec.toString);
275
    }
276
}
277

278
alias TimeStamp = TTimeStamp!false; /// Unknown TZ timestamp
279
alias TimeStampUTC = TTimeStamp!true; /// Assumed that this is UTC timestamp
280

281
unittest
282
{
283 1
    auto t = TimeStamp(DateTime(2017, 11, 13, 14, 29, 17), 75_678.usecs);
284 1
    assert(t.dateTime.hour == 14);
285
}
286

287
unittest
288
{
289 1
    auto dt = DateTime(2017, 11, 13, 14, 29, 17);
290 1
    auto t = TimeStamp(dt, 75_678.usecs);
291

292 1
    assert(t.dateTime == dt); // test the implicit conversion to DateTime
293
}
294

295
unittest
296
{
297 1
    auto t = TimeStampUTC(
298
            DateTime(2017, 11, 13, 14, 29, 17),
299
            75_678.usecs
300
        );
301

302 1
    assert(t.dateTime.hour == 14);
303 1
    assert(t.fracSec == 75_678.usecs);
304
}
305

306
unittest
307
{
308
    import std.exception : assertThrown;
309

310 1
    auto e = TimeStampUTC.earlier;
311 1
    auto l = TimeStampUTC.later;
312

313 1
    assertThrown!ValueConvException(e.dateTime.hour == 14);
314 1
    assertThrown!ValueConvException(l.dateTime.hour == 14);
315
}
316

317
/// Oid tests
318
unittest
319
{
320 1
    assert(detectOidTypeFromNative!TimeStamp == OidType.TimeStamp);
321 1
    assert(detectOidTypeFromNative!TimeStampUTC == OidType.TimeStampWithZone);
322 1
    assert(detectOidTypeFromNative!SysTime == OidType.TimeStampWithZone);
323 1
    assert(detectOidTypeFromNative!Date == OidType.Date);
324 1
    assert(detectOidTypeFromNative!TimeOfDay == OidType.Time);
325
}
326

327
package enum POSTGRES_EPOCH_DATE = Date(2000, 1, 1);
328
package enum POSTGRES_EPOCH_JDATE = POSTGRES_EPOCH_DATE.julianDay;
329
static assert(POSTGRES_EPOCH_JDATE == 2_451_545); // value from Postgres code
330

331
private:
332

333
T rawTimeStamp2nativeTime(T)(long raw)
334
if(is(T == TimeStamp) || is(T == TimeStampUTC))
335
{
336
    import core.stdc.time: time_t;
337

338 1
    if(raw == long.max) return T.later; // infinity
339 1
    if(raw == long.min) return T.earlier; // -infinity
340

341 1
    pg_tm tm;
342 1
    fsec_t ts;
343

344 1
    if(timestamp2tm(raw, tm, ts) < 0)
345 0
        throw new ValueConvException(
346
            ConvExceptionType.OUT_OF_RANGE, "Timestamp is out of range",
347
        );
348

349 1
    TimeStamp ret = raw_pg_tm2nativeTime(tm, ts);
350

351
    static if(is(T == TimeStamp))
352 1
        return ret;
353
    else
354 1
        return TimeStampUTC(ret.dateTime, ret.fracSec);
355
}
356

357
TimeStamp raw_pg_tm2nativeTime(pg_tm tm, fsec_t ts)
358
{
359 1
    return TimeStamp(
360
        PgDate(
361
            tm.tm_year,
362
            cast(ubyte) tm.tm_mon,
363
            cast(ubyte) tm.tm_mday
364
        ),
365
        TimeOfDay(
366
            tm.tm_hour,
367
            tm.tm_min,
368
            tm.tm_sec
369
        ),
370
        ts.dur!"usecs"
371
    );
372
}
373

374
// Here is used names from the original Postgresql source
375

376
void j2date(int jd, out int year, out int month, out int day)
377
{
378
    enum MONTHS_PER_YEAR = 12;
379

380 1
    jd += POSTGRES_EPOCH_JDATE;
381

382 1
    uint julian = jd + 32044;
383 1
    uint quad = julian / 146097;
384 1
    uint extra = (julian - quad * 146097) * 4 + 3;
385 1
    julian += 60 + quad * 3 + extra / 146097;
386 1
    quad = julian / 1461;
387 1
    julian -= quad * 1461;
388 1
    int y = julian * 4 / 1461;
389 1
    julian = ((y != 0) ? ((julian + 305) % 365) : ((julian + 306) % 366))
390
        + 123;
391 1
    year = (y+ quad * 4) - 4800;
392 1
    quad = julian * 2141 / 65536;
393 1
    day = julian - 7834 * quad / 256;
394 1
    month = (quad + 10) % MONTHS_PER_YEAR + 1;
395
}
396

397
private alias long Timestamp;
398
private alias long TimestampTz;
399
private alias long TimeADT;
400
private alias long TimeOffset;
401
private alias int  fsec_t;      /* fractional seconds (in microseconds) */
402

403
void TMODULO(ref long t, ref long q, double u)
404
{
405 1
    q = cast(long)(t / u);
406 1
    if (q != 0) t -= q * cast(long)u;
407
}
408

409
TimeOfDay time2tm(TimeADT time)
410
{
411 1
    immutable long USECS_PER_HOUR  = 3600000000;
412 1
    immutable long USECS_PER_MINUTE = 60000000;
413 1
    immutable long USECS_PER_SEC = 1000000;
414

415 1
    int tm_hour = cast(int)(time / USECS_PER_HOUR);
416 1
    time -= tm_hour * USECS_PER_HOUR;
417 1
    int tm_min = cast(int)(time / USECS_PER_MINUTE);
418 1
    time -= tm_min * USECS_PER_MINUTE;
419 1
    int tm_sec = cast(int)(time / USECS_PER_SEC);
420 1
    time -= tm_sec * USECS_PER_SEC;
421

422 1
    return TimeOfDay(tm_hour, tm_min, tm_sec);
423
}
424

425
struct pg_tm
426
{
427
    int         tm_sec;
428
    int         tm_min;
429
    int         tm_hour;
430
    int         tm_mday;
431
    int         tm_mon;         /* origin 0, not 1 */
432
    int         tm_year;        /* relative to 1900 */
433
    int         tm_wday;
434
    int         tm_yday;
435
    int         tm_isdst;
436
    long        tm_gmtoff;
437
    string      tm_zone;
438
}
439

440
alias pg_time_t = long;
441

442
enum USECS_PER_DAY       = 86_400_000_000UL;
443
enum USECS_PER_HOUR      = 3_600_000_000UL;
444
enum USECS_PER_MINUTE    = 60_000_000UL;
445
enum USECS_PER_SEC       = 1_000_000UL;
446

447
/**
448
* timestamp2tm() - Convert timestamp data type to POSIX time structure.
449
*
450
* Note that year is _not_ 1900-based, but is an explicit full value.
451
* Also, month is one-based, _not_ zero-based.
452
* Returns:
453
*   0 on success
454
*  -1 on out of range
455
*
456
* If attimezone is null, the global timezone (including possibly brute forced
457
* timezone) will be used.
458
*/
459
int timestamp2tm(Timestamp dt, out pg_tm tm, out fsec_t fsec)
460
{
461 1
    Timestamp   date;
462 1
    Timestamp   time;
463 1
    pg_time_t   utime;
464

465 1
    time = dt;
466 1
    TMODULO(time, date, USECS_PER_DAY);
467

468 1
    if (time < 0)
469
    {
470 0
        time += USECS_PER_DAY;
471 0
        date -= 1;
472
    }
473

474 1
    j2date(cast(int) date, tm.tm_year, tm.tm_mon, tm.tm_mday);
475 1
    dt2time(time, tm.tm_hour, tm.tm_min, tm.tm_sec, fsec);
476

477 1
    return 0;
478
}
479

480
void dt2time(Timestamp jd, out int hour, out int min, out int sec, out fsec_t fsec)
481
{
482 1
    TimeOffset  time;
483

484 1
    time = jd;
485 1
    hour = cast(int)(time / USECS_PER_HOUR);
486 1
    time -= hour * USECS_PER_HOUR;
487 1
    min = cast(int)(time / USECS_PER_MINUTE);
488 1
    time -= min * USECS_PER_MINUTE;
489 1
    sec = cast(int)(time / USECS_PER_SEC);
490 1
    fsec = cast(int)(time - sec*USECS_PER_SEC);
491
}

Read our documentation on viewing source code .

Loading