github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/docs/RFCS/20161006_time_conversion.md (about)

     1  - Feature Name: conversions between time and strings
     2  - Status: completed
     3  - Start Date: 2016-10-06
     4  - Authors: mjibson, dt, knz
     5  - RFC PR: #9790
     6  - Cockroach Issue: #9786 #9762 #14801
     7  
     8  # Summary
     9  
    10  We and users want functions to convert between strings and time/date
    11  values. Which functions should we provide?
    12  
    13  This RFC compares the alternatives:
    14  - do what another RDBMs does
    15  - strftime/strptime
    16  - go format/parse
    17  
    18  The outcome of the RFC (with the stated scope to merely establish a
    19  comparison) suggests a tentative advantage for the strftime/strptime
    20  interface, leaving the door open for users to tell us that they would
    21  also like functions using descriptive formats, later.
    22  
    23  (Side note: CockroachDB currently provides "experimental" built-in
    24  functions for strptime/strftime using http://github.com/knz/strtime;
    25  the formats supported are symmetric across parsing and formatting.)
    26  
    27  # What other RDBMs do
    28  
    29  - Pretty much standard: converting via casts. This always uses the
    30    canonical timestamp/date format; no customization is
    31    possible. CockroachDB already supports that too.
    32  
    33  - PostgreSQL: https://www.postgresql.org/docs/8.1/static/functions-formatting.html
    34  
    35    This interface is pretty reminiscent of COBOL text I/O.  It has a
    36    myriad of options; there are numerous edge cases and the behavior is
    37    surprising at times. Rather complex to implement.
    38  
    39    Oracle supports more or less the same interface.
    40  
    41  - SQLite: https://sqlite.org/lang_datefunc.html
    42  
    43    Supports `strftime` plus a couple of functions that use
    44    human-friendly format strings like `"YYYY-MM-DD HH:MM"`. The list of
    45    supported formats is fixed though.
    46  
    47    No function for parsing.
    48  
    49  - MySQL: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html
    50  
    51    `STR_TO_DATE` and `STR_TO_TIME` are pretty much `strptime` in
    52    disguise with MySQL-specific extensions.
    53    `DATE_FORMAT` is pretty much `strftime` in disguise and supports the
    54    same format codes.
    55    Supports functions to extract individual fields of a time/date:
    56    `WEEK`, `YEAR`, etc.
    57  
    58  - SQL Server (Transact-SQL): https://msdn.microsoft.com/en-us/library/ms187928.aspx
    59  
    60    Uses `CONVERT` with a predetermined fixed set of formats.
    61  
    62  # Proposed approaches
    63  
    64  The initial implementation in #9762 was to propose
    65  strftime/strptime. Then Matt came up with the argument that Go's
    66  Format/Parse format strings may provide better UX, so we could propose
    67  SQL functions that simply interface with those.
    68  
    69  Comparative analysis:
    70  
    71  - strftime/strptime:
    72    - Pros:
    73      - compatibility: it's what mysql/sqlite already do.
    74      - mindshare: what most programming languages propose already, so plenty of docs online.
    75      - usability: enables interleaving arbitrary text and numbers between formatted values.
    76      - performance: parsing the format string is faster for the underlying function.
    77    - Cons:
    78      - no working native Go implementation of strptime as of now,
    79        so need to use a lib that wraps C's strptime with cgo.
    80        - This can be alleviated by a Go re-implementation, if/when performance
    81          becomes a concern.
    82  
    83  - Go Format/Parse:
    84    - Pros:
    85      - performance: no call via cgo.
    86      - usability: format strings are more readable (Matt's argument).
    87  	  - Ben: readability here depends on which format is used. For
    88          "2006-01-02T15:04:05Z07:00" the argument holds, but for
    89          "060102 15:04:05" (our log format) it's not immediately
    90          obvious what the first field encodes.
    91    - Cons:
    92      - mindshare: quite Go-specific.
    93      - usability: timezone support is so-so (time not adjusted during parsing)
    94  
    95  Regarding usability we can also take note of SQLite which proposes
    96  both strftime and conversions using human-readable formats.
    97  
    98  # Additional concerns
    99  
   100  As was discovered in the initial implementation, delegating the work
   101  to the underlying C library yields surprising and potentially
   102  incorrect behavior: given the support for format string is different
   103  across platforms and libc versions, a query would yield different
   104  results depending on which node it is run (on an heterogeneous
   105  cluster).
   106  
   107  This was averted in #14801 by embedding a particular implementation
   108  common to all Go target platforms.
   109  
   110  # Unresolved questions
   111  
   112  Where do we want to go?  Currently CockroachDB supports
   113  `experimental_strftime` and `experimental_strptime`. User interest
   114  needs to be gauged, either to retire these built-ins entirely or
   115  migrate them out of `experimental_xxx`.