New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Is there any reason to not use Date as a type for dates? #122

Open
iglpdc opened this Issue Jan 14, 2016 · 4 comments

Comments

Projects
None yet
4 participants
@iglpdc
Contributor

iglpdc commented Jan 14, 2016

We use TEXT as the type for dated in the Visited table, but SQLite has a DATE type which I think is more appropriated here.

I'd prefer DATE because it works the same, but also is able to handle different formats or explain how dates can be ordered.

Is there any reason not to use it?

@gvwilson

This comment has been minimized.

Member

gvwilson commented Jan 14, 2016

@wking

This comment has been minimized.

Member

wking commented Jan 14, 2016

On Thu, Jan 14, 2016 at 12:12:42PM -0800, Ivan Gonzalez wrote:

We use TEXT as the type for dated in the Visited table, but
SQLite has a DATE type which I think is more appropriated here.

SQLite doesn't actually have a DATE type 1, it just interprets
‘DATE’ as ‘NUMERIC’ 2. I'd guess the reason to prefer TEXT would be
to get ISO 8601 strings in SELECT results, but DATE would have better
compatibility with other SQL engines. For our audience, the former
seems more important.

 Anchor for §2.1, but I'd like to link to “2.2 Affinity Name
 Examples”.
@iglpdc

This comment has been minimized.

Contributor

iglpdc commented Jan 17, 2016

I'd guess the reason to prefer TEXT would be to get ISO 8601 strings in SELECT results, but DATE
would have better compatibility with other SQL engines.

Not sure about what you mean. I understand from here [1] that SQLite DATE is output as an ISO 8601 string.

[1] https://www.sqlite.org/lang_datefunc.html

@wking

This comment has been minimized.

Member

wking commented Jan 18, 2016

On Sun, Jan 17, 2016 at 09:06:29AM -0800, Ivan Gonzalez wrote:

I'd guess the reason to prefer TEXT would be to get ISO 8601
strings in SELECT results, but DATE would have better
compatibility with other SQL engines.

Not sure about what you mean. I understand from here 1 that SQLite
DATE is output as an ISO 8601 string.

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

That page is about the date functions, not about the date datatype
(which SQLite emulates using non-DATE affinities 1). But typing is
a hint in SQLite, you can store any data type in any column 2.

For example:

$ sqlite3
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE dates (date DATE);
sqlite> INSERT INTO dates VALUES ('1927-02-08');
sqlite> INSERT INTO dates VALUES (-1353513600);
sqlite> SELECT date, typeof(date) FROM dates;
1927-02-08|text
-1353513600|integer

The date functions just let you convert between formats (e.g. from
timestamps to ISO 8601:

sqlite> SELECT datetime(date, 'unixepoch', 'localtime') FROM dates WHERE typeof(date) = 'integer';
1927-02-10 00:00:00

But having re-read those docs, I'll change my earlier position and say
that DATE will be fine. As long as we feed it ISO 8601 strings,
SQLite will give us those strings back on SELECT. And other database
engines which actually enforce types will be happier with DATE ;).

 “The important idea here is that the type is recommended, not
 required. Any column can still store any type of data. It is just
 that some columns, given the choice, will prefer to use one
 storage class over another. The preferred storage class for a
 column is called its "affinity".”

@gvwilson gvwilson self-assigned this Jul 31, 2016

@gvwilson gvwilson removed their assignment Sep 10, 2016

rgaiacs added a commit to rgaiacs/swc-sql-novice-survey that referenced this issue Mar 13, 2017

Merge pull request swcarpentry#122 from gvwilson/commenting-includes
Adding comments on purposes of include files
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment