Uploaded image for project: 'Engineering'
  1. Engineering
  2. ENG-3008

As a user, I want to use simple (TIME ZONE ignorant) SQL datetime value functions, so that I can migrate my (timezone-ignorant) SQL app that uses them.

    XMLWordPrintable

    Details

    • Type: Story
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: IT17-v2.6
    • Fix Version/s: V4.0
    • Component/s: Core
    • Team Backlog:
      CORE team
    • Epic Link:
    • Story Points:
      8
    • Sprint:
      core18, core19, core20

      Description

      First pass: only support NOW() (and alias CURRENT_TIMESTAMP).

      Make sure it can be used as a default value.

      ---------------
      ---------------
      The functions to be implemented are CURRENT_DATE, LOCALTIME [ ( <time precision> ) ], and LOCALTIMESTAMP [ ( <timestamp precision> ) ],
      CURRENT_TIME [ ( <time precision> ) ], and CURRENT_TIME [ ( <time precision> ) ].

      For determinism, all of these functions will return a value corresponding to the start time/date of the original invocation of the current transaction.

      For ease of migration, the following aliases (supported by hsqldb, as currently documented) should also be supported:
      CURDATE()
      CURTIME()
      NOW() (for CURRENT_TIMESTAMP)

      CURRENT_TIME and CURRENT_TIMESTAMP support is specified in the standard as part of a separate feature, F411, "Time zone specification"
      and would represent a non-compliant partial implementation of that feature.

      For quicker time-to-market, all functions will take advantage of the current restriction that the local time zone has the fixed setting of UTC.

      Syntax Rules

      According to the standard,

      The declared type of CURRENT_DATE is DATE.

      -------------
      The declared type of LOCALTIME is TIME WITHOUT TIME ZONE. The declared type of LOCALTIMESTAMP is TIMESTAMP WITHOUT TIME ZONE.
      LOCALTIME(TP) is equivalent to: CAST (CURRENT_TIME [ (<time precision>) ] AS TIME[ (<time precision>) ] WITHOUT TIME ZONE)
      LOCALTIMESTAMP(TP) is equivalent to: CAST (CURRENT_TIMESTAMP[ (<time precision>) ] AS TIMESTAMP[ (<time precision>) ] WITHOUT TIME ZONE)
      CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP respectively return the current date, current time, and current timestamp; the time and timestamp values are returned with time zone displacement equal to the current default time zone displacement of the SQL- session.
      If specified, <time precision> and <timestamp precision> respectively determine the precision of the time or timestamp value returned.

      The declared type of CURRENT_TIME is TIME WITH TIME ZONE. The declared type of CURRENT_TIMESTAMP is TIMESTAMP WITH TIME ZONE.
      LOCALTIME(TP) is equivalent to: CAST (CURRENT_TIME [ (<time precision>) ] AS TIME[ (<time precision>) ] WITHOUT TIME ZONE)
      LOCALTIMESTAMP(TP) is equivalent to: CAST (CURRENT_TIMESTAMP[ (<time precision>) ] AS TIMESTAMP[ (<time precision>) ] WITHOUT TIME ZONE)
      CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP respectively return the current date, current time, and current timestamp; the time and timestamp values are returned with time zone displacement equal to the current default time zone displacement of the SQL- session.
      If specified, <time precision> and <timestamp precision> respectively determine the precision of the time or timestamp value returned.

      Note, these will not be supported as our "local" time is GMT:

        Attachments

          Activity

            People

            • Assignee:
              xin Xin Jia
              Reporter:
              pmartel Paul Martel
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Zendesk Support