postgresql-simple 0.5.1.1 → 0.5.1.2

raw patch · 5 files changed

+95/−67 lines, 5 files

Files

CONTRIBUTORS +1/−0
postgresql-simple.cabal +2/−2
src/Database/PostgreSQL/Simple/FromRow.hs +2/−1
src/Database/PostgreSQL/Simple/Time.hs +81/−58
src/Database/PostgreSQL/Simple/ToField.hs +9/−6

CONTRIBUTORS view

@@ -29,3 +29,4 @@ Timmy Tofu <timmytofu@users.noreply.github.com> Alexey Khudyakov <alexey.skladnoy@gmail.com> Timo von Holtz <tvh@anchor.com.au>+Amit Levy <amit@amitlevy.com>

postgresql-simple.cabal view

@@ -1,5 +1,5 @@ Name:                postgresql-simple-Version:             0.5.1.1+Version:             0.5.1.2 Synopsis:            Mid-Level PostgreSQL client library Description:     Mid-Level PostgreSQL client library, forked from mysql-simple.@@ -87,7 +87,7 @@ source-repository this   type:     git   location: http://github.com/lpsmith/postgresql-simple-  tag:      v0.5.1.1+  tag:      v0.5.1.2  test-suite test   type:           exitcode-stdio-1.0

src/Database/PostgreSQL/Simple/FromRow.hs view

@@ -56,7 +56,8 @@ -- Note that instances can be defined outside of postgresql-simple,  which is -- often useful.   For example, here's an instance for a user-defined pair: ----- @data User = User { name :: String, fileQuota :: Int }+-- @+-- data User = User { name :: String, fileQuota :: Int } -- -- instance 'FromRow' User where --     fromRow = User \<$\> 'field' \<*\> 'field'

src/Database/PostgreSQL/Simple/Time.hs view

@@ -9,23 +9,23 @@ infinity,  as well as some functions for converting to and from strings.  Also, this module also contains commentary regarding postgresql's timestamp-types,  timekeeping in general,  and how it relates to postgresql-simple.-You can read more about PostgreSQL's date and time types at-<http://www.postgresql.org/docs/9.1/static/datatype-datetime.html>,-and the IANA timezone database at <https://en.wikipedia.org/wiki/Tz_database>.+types,  civil timekeeping in general,  and how it relates to+postgresql-simple. You can read more about PostgreSQL's date and time types+at <http://www.postgresql.org/docs/9.1/static/datatype-datetime.html>,+and the IANA time zone database at <https://en.wikipedia.org/wiki/Tz_database>.  Stack Overflow also has some excellent commentary on time,  if it is a wiki page or a highly upvoted question and answer.   If the answer regarding-time has not received hundreds of upvotes,  then the answer is almost-invariably completely and painfully wrong,  even if it's the chosen answer-or the most highly upvoted answer to a question.+time has not received about a hundred upvotes at least,  then the answer is+almost invariably completely and painfully wrong,  even if it's the chosen+answer or the most highly upvoted answer to a question.  PostgreSQL's @timestamp with time zone@ (hereafter, @timestamptz@) can be converted to Haskell's 'Data.Time.UTCTime' and 'Data.Time.ZonedTime' types,-because their values represent an unambiguous point in time.   PostgreSQL's-@timestamp without time zone@ (hereafter, @timestamp@) can be converted-to Haskell's 'Data.Time.LocalTime',  because values of these types can only be-disambiguated in a larger context.+because values of these types represent a self-contained, unambiguous point+in time.  PostgreSQL's @timestamp without time zone@ (hereafter, @timestamp@)+can be converted to Haskell's 'Data.Time.LocalTime',  because values of these+types are ambiguous by themselves,  and require context to disambiguate.  While this behavior may be superficially counterintuitive because the names might suggest otherwise,  this behavior is correct.   In fact,@@ -42,44 +42,54 @@ (which may vary throughout the year, due to daylight savings) that a region follows,  such as Universal Coordinated Time (UTC), Eastern Standard Time\/Eastern Daylight Time (EST\/EDT), or India Standard Time (IST).-And a timezone,  much like a standard time, is a function from+And a time zone, much like a standard time, is a function from timestamps to offsets. -A timezone is different from a standard time because different regions-inside a standard time are governed by different civil authorities and-have different histories of time.  Thus, an IANA timezone is any region-of the world that has had the same history of time since-@1970-01-01 00:00+00@.+A time zone is different from a standard time because different regions+inside a standard time can be governed by different civil authorities with+different laws and thus have different histories of civil time.  An IANA+time zone is any region of the world that has had the same history of+civil time since @1970-01-01 00:00+00@.  For example, as of today,  both @America\/New_York@ and @America\/Indiana\/Indianapolis@ are on the EST\/EDT time standard,  but Indiana used to be on Central Standard Time until 1942, and did not observe daylight savings time (EST only) until 2006.   Thus,  the choice between-these two timezones still matters if you are dealing with timestamps+these two time zones still matters if you are dealing with timestamps prior to 2006,  and could become relevant again if (most of) Indiana moves back to Central Time.  (Of course,  if the Central to Eastern switch-was the only difference,  then these two timezones would be the same in+was the only difference,  then these two time zones would be the same in IANA's eyes,  due to their cutoff date of 1970-01-01.) -Getting back to the coding practicalities,  PostgreSQL's @timestamptz@-type does not actually store an offset;  rather, it uses the offset-provided to calculate UTC, and stores the timestamp as UTC.   If an-offset is not provided,  the given timestamp is assumed to be a local-time for whatever the @timezone@ variable is set to, and the IANA TZ-database is consulted to calculate an offset from UTC for the time in-question.   Note that some local timestamps are ambiguous,  in which-case PostgreSQL chooses one of the possible offsets.+Getting back to practicalities,  PostgreSQL's @timestamptz@ type does not+actually store an offset;  rather, it uses the offset provided to calculate+UTC, and stores the timestamp as UTC.   If an offset is not provided, the+given timestamp is assumed to be a local time for whatever the @timezone@+variable is set to, and the IANA TZ database is consulted to calculate an+offset from UTC for the time in question. -When retrieving a @timestamptz@,  the backend looks at the @timezone@+Note that while most (local timestamp, time zone) pairs correspond to exactly+one UTC timestamp, some correspond to two UTC timestamps,  while others+correspond to none at all.   The ambiguous case occurs when the civil time+is rolled back,  making a calendar day longer than 24 hours.  In this case,+PostgreSQL silently chooses the second, later possibility.  The inconsistent+case occurs when the civil time is moved forward,  making a calendar day less+than 24 hours.  In this case,  PostgreSQL silently assumes the local time+was read off a clock that had not been moved forward at the prescribed time,+and moves the clock forward for you.   Thus,  converting from local time+to UTC need not be monotonic,  if these inconsistent cases are allowed.++When retrieving a @timestamptz@,  the backend looks at the @time zone@ connection variable and then consults the IANA TZ database to calculate-an offset for the timestamp in the given timezone.  Note that while some-of the information contained in the IANA TZ database is a bit of a-standardized fiction, the conversion from UTC time to a-(local time, offset) pair is always unambiguous,  and the result can-always be unambiguously converted back to UTC.  Thus, postgresql-simple-can interpret this result as a 'Data.Time.ZonedTime',  or use the offset to-convert to 'Data.Time.UTCTime'.+an offset for the timestamp in the given time zone. +Note that while some of the information contained in the IANA TZ database+is a bit of a standardized fiction, the conversion from UTC time to a+(local time, offset) pair in a particular time zone is always unambiguous,+and the result can always be unambiguously converted back to UTC.  Thus,+postgresql-simple can interpret such a result as a 'Data.Time.ZonedTime',+or use the offset to convert back to 'Data.Time.UTCTime'.+ By contrast, the @timestamp@ type ignores any offsets provided to it, and never sends back an offset.   Thus,  postgresql-simple equates this with 'Data.Time.LocalTime',  which has no concept of an offset.  One can@@ -87,55 +97,68 @@ operator, whose semantics also demonstrates that @timestamptz@ is 'Data.Time.UTCTime' whereas @timestamp@ is 'Data.Time.LocalTime'. -The default default setting for the @timezone@ variable is @\'localtime\'@,-which normally corresponds to the server's local timezone.  However,-this default can be modified on the server side for an entire cluster, or-on a per-user or per-database basis.  On the client side, the timezone can-be set via the @PGTZ@ environment variable on the client, or be modified at-any time for the duration of the entire connection,  just a single-transaction,  or the execution context of a server-side function.+PostgreSQL's @timezone@ is a per-connection variable that by default is+initialized to @\'localtime\'@,  which normally corresponds to the server's+time zone.  However, this default can be modified on the server side for an+entire cluster, or on a per-user or per-database basis.  Moreover, a client+can modify their instance of the variable at any time,  and can apply that+change to the remaining duration of the connection, the current transaction,+or the execution context of a server-side function.  In addition, upon+connection initialization, the libpq client checks for the existence of+the @PGTZ@  environment variable, and if it exists, modifies @timezone@+accordingly.  With a few caveats,  postgresql-simple is designed so that you can both send and receive timestamps with the server and get a correct result,  no matter what the @timezone@ setting is.  But it is important to understand the caveats:  1. The correctness of server-side computations can depend on the @timezone@-   setting,  for example when adding an @interval@ to a @timestamptz@, or-   when type casting between @timestamp@ and @timestamptz@.+   setting.  Examples include adding an @interval@ to a @timestamptz@, or+   type casting between @timestamp@ and @timestamptz@,  or applying+   the @DATE@ function to a @timestamptz@. -2. The offset in a 'Data.Time.ZonedTime' result will depend on the-   @timezone@ setting,  although the result will always represent the-   same instant in time regardless of the offset.+2. The (localtime, offset) pair contained in a 'Data.Time.ZonedTime' result+   will depend on the @timezone@ setting,  although the result will always+   represent the same instant in time regardless of the time zone.  3. Sending a 'Data.Time.LocalTime' and interpreting it as a @timestamptz@    can be useful,  as it will be converted to UTC via the tz database,    but correctness will depend on the @timezone@ setting.   You may prefer-   to use an explicit @AT TIME ZONE@ instead.+   to use an explicit @AT TIME ZONE@ conversion instead, which would avoid+   this contextual dependence. -Furthermore,  although these points don't involve the @timezone@ setting,-they are related to the last point above:+Furthermore,  although these following points don't involve the @timezone@+setting, they are related to the last point above:  1. Sending a 'Data.Time.UTCTime' and interpreting it as a @timestamp@ can    be useful.  In practice,  the most common context used to disambiguate    @timestamp@ is that it represents UTC,  and this coding technique will-   work as expected in this context.+   work as expected in this situation.  2. Sending a 'Data.Time.ZonedTime' and interpreting it as a @timestamp@ is    almost always the wrong thing to do,  as the offset will be ignored and    discarded.  This is likely to lead to inconsistencies in the database,-   and may lead to partial data loss.  Moreover, one should keep in mind-   that "ZonedTime" is misnamed,  and one should not conflate an offset-   with a timezone.+   and may lead to partial data loss. +When dealing with local timestamps that refer to the future,  it is often+useful to store it as a local time in a @timestamp@ column and store the+time zone in a second column.  One reason to do this is so that you can+convert to UTC on the fly as needed, and be protected against future changes+to the TZ database due to changes in local time standards.  In any case,+'Data.Time.ZonedTime' is not suitable for this application, because despite+its name,  it represents an offset and not a time zone.  Time zones can change;+offsets do not.  In reality,  we can't convert a local timestamp that occurs+sufficiently far in the future to UTC, because we don't know how to do it yet.+ There are a few limitations and caveats that one might need to be aware of with the current implementation when dealing with older timestamps: -For sufficiently old timestamps in almost all timezones,  the IANA TZ+For sufficiently old timestamps in almost all time zones,  the IANA TZ database specifies offsets from UTC that is not an integral number of minutes.   This corresponds to local mean time;  that is, astronomical-time in the city that defines the timezone.  Different timezones moved+time in the city that defines the time zone.  Different time zones moved away from local mean time to a standard time at different points in-history,  so \"sufficiently old\" depends on the timezone in question.+history,  so \"sufficiently old\" depends on the time zone in question.  Thus, when retrieving a @timestamptz@ postgresql will in some cases provide seconds in the offset.  For example:@@ -176,7 +199,7 @@ print or especially parse.  Also, postgresql itself cannot parse or print dates before @4714-11-24 BC@,- which is the Julian date on the proleptic Gregorian Calendar.   Although+which is the Julian date on the proleptic Gregorian Calendar.   Although postgresql's timestamp types are perfectly capable of representing timestamps nearly 300,000 years in the past,  using this would require postgresql-simple and other client programs to support binary parameters and results.

src/Database/PostgreSQL/Simple/ToField.hs view

@@ -272,12 +272,15 @@     {-# INLINE toField #-}  instance (ToField a) => ToField (PGArray a) where-    toField xs = Many $-        Plain (byteString "ARRAY[") :-        (intersperse (Plain (char8 ',')) . map toField $ fromPGArray xs) ++-        [Plain (char8 ']')]-        -- Because the ARRAY[...] input syntax is being used, it is possible-        -- that the use of type-specific separator characters is unnecessary.+    toField pgArray =+      case fromPGArray pgArray of+        [] -> Plain (byteString "'{}'")+        xs -> Many $+          Plain (byteString "ARRAY[") :+          (intersperse (Plain (char8 ',')) . map toField $ xs) +++          [Plain (char8 ']')]+          -- Because the ARRAY[...] input syntax is being used, it is possible+          -- that the use of type-specific separator characters is unnecessary.  instance (ToField a) => ToField (Vector a) where     toField = toField . PGArray . V.toList