Issue #16005 has been updated by akr (Akira Tanaka).


(1) The SQL spec. defines the range of year as 0001 to 9999.
(not in the syntax but in another table.)

However Ruby Time object can represent arbitrary integer year.

There is a RDB which works with a year outside of 0001 to 9999.
It seems SQLite3 supports -4173 to 9999.
(-4713-11-24 12:00:00 is Julian day number zero in the proleptic Gregorian calendar).

```
% sqlite3
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT datetime("-4713-11-24 11:59:59");

sqlite> SELECT datetime("-4713-11-24 12:00:00");
-4713-11-24 12:00:00
sqlite> SELECT datetime("9999-12-31 23:59:59");
9999-12-31 23:59:59
sqlite> SELECT datetime("10000-01-01 00:00:00");

sqlite> 
```

I feel our method should extend the "years value" syntax with optional "minus sign" at
beginning.

(2) Another mismatch between Ruby's Time and SQL timestamp is
a resolution of timezone offset.
Ruby can represent Rational offset but SQL supports only minutes.
Olson timezone database supports seconds.
Practically, minutes is enough for current timezones.
But there is a historical timezone which use a offset not multiple of 60 seconds:
Europe/Lisbon has a timezone offset -2205.

```
% zdump -v Europe/Lisbon|head -4
Europe/Lisbon  -9223372036854775808 = NULL
Europe/Lisbon  -9223372036854689408 = NULL
Europe/Lisbon  Sun Dec 31 23:59:59 1911 UT = Sun Dec 31 23:23:14 1911 LMT isdst=0 gmtoff=-2205
Europe/Lisbon  Mon Jan  1 00:00:00 1912 UT = Mon Jan  1 00:00:00 1912 WET isdst=0 gmtoff=0
```

I feel that we can ignore such offset, though.

(3) Method name idea: `sql_timestamp`


----------------------------------------
Feature #16005: A variation of Time.iso8601 that can parse yyyy-MM-dd HH:mm:ss
https://bugs.ruby-lang.org/issues/16005#change-79682

* Author: matsuda (Akira Matsuda)
* Status: Open
* Priority: Normal
* Assignee: 
* Target version: 
----------------------------------------
Let me propose a String to Time conversion method that can parse "yyyy-MM-dd HH:mm:ss" format, which is very much similar to `Time.iso8601`, but delimits the date part and the time part with a space character.

This format is defined as the "timestamp string" literal in SQL 92 standard: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt (see P. 90)
and so this format is very widely used as the default datetime / timestamp literal for major existing RDBMS implementations.

Oracle
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Literals.html#GUID-8F4B3F82-8821-4071-84D6-FBBA21C05AC1

SQL Server
https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/date-time-and-timestamp-literals?view=sql-server-2017

PostgreSQL
https://www.postgresql.org/docs/11/datatype-datetime.html#id-1.5.7.13.19.7.2

MySQL
https://dev.mysql.com/doc/refman/8.0/en/datetime.html

SQLite3
https://www.sqlite.org/lang_datefunc.html

In order to handle this conversion in Ruby on Rails framework, we define our own String => Time conversion method
https://github.com/rails/rails/blob/b4c715fe/activemodel/lib/active_model/type/helpers/time_value.rb#L62-L76
and Time => String conversion for now,
https://github.com/rails/rails/blob/b4c715fe/activesupport/lib/active_support/core_ext/time/conversions.rb#L7-L59
and I think it's nicer if we had them in the language level with a faster implementation.

As for the method name, maybe we can name it `Time.sql92`, `Time.sql`, `Time.parse_sql92` or whatever, or maybe we can add an option to `Time.iso8601` if it could be regarded as a variation of `Time.iso8601`? (https://en.wikipedia.org/wiki/ISO_8601#cite_note-30)



-- 
https://bugs.ruby-lang.org/

Unsubscribe: <mailto:ruby-core-request / ruby-lang.org?subject=unsubscribe>
<http://lists.ruby-lang.org/cgi-bin/mailman/options/ruby-core>