Re: [sqlite] Date time input

[Thomas Kurz]

> I hope you will experience such joy as well. 

Well, I don't ;-) The lack of full ALTER TABLE support frustrates me every 
time, even though I greatly appreciate most other parts of SQLite and the 
developers' work. But a more complete SQL statement support would be very, very 
helpful.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[Mark Brand]

On 10/10/2019 07:50, J Decker wrote:


It's 'ite' as in 'stalagmite' or 'meteorite' as in rock solid...
https://changelog.com/podcast/201

"
RICHARD HIPP 

How do I pronounce the name of the product? I say S-Q-L-ite, like a mineral.

So probably Sqlism (as in organism) :)



Hmm. If the enriched version has more metaphorical oxygen, you could 
call it sqlate.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[J Decker]

On Wed, Oct 9, 2019 at 9:05 PM Keith Medcalf  wrote:

>
> On Tuesday, 8 October, 2019 12:40, James K. Lowden <
> jklow...@schemamania.org> wrote:
>
> >OK, but it needs a better name.  What better place than here to debate
> >that?  ;-)
>
> >What the opposite of "Lite"?
>
> It's 'ite' as in 'stalagmite' or 'meteorite' as in rock solid...

https://changelog.com/podcast/201

"
RICHARD HIPP 

How do I pronounce the name of the product? I say S-Q-L-ite, like a mineral.

So probably Sqlism (as in organism) :)


> I believe the PC euphemism is Big and Tall.
>
> SQLBAT3?
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[Keith Medcalf]

On Tuesday, 8 October, 2019 12:40, James K. Lowden  
wrote:

>OK, but it needs a better name.  What better place than here to debate
>that?  ;-)

>What the opposite of "Lite"?

I believe the PC euphemism is Big and Tall.

SQLBAT3?

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[Graham Holden]

Tuesday, October 08, 2019, 7:39:40 PM, James K. Lowden 
 wrote:

> OK, but it needs a better name.  What better place than here to debate
> that?  ;-)
>

SQLOfALot

(providing you pronounce SQL as ess-queue-ell and not sequel)

Graham


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[Gary R. Schmidt]

On 09/10/2019 10:25, Kevin Benson wrote:

On Tue, Oct 8, 2019 at 2:40 PM James K. Lowden 
wrote:


On Tue, 8 Oct 2019 09:06:24 -0700
Jens Alfke  wrote:


I think the idea of a semi-official ?SQLite++? has been floated here
before


OK, but it needs a better name. --



SQLiteXTD


SQLArdArse.

Cheers,
GaryB-)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[Kevin Benson]

On Tue, Oct 8, 2019 at 2:40 PM James K. Lowden 
wrote:

> On Tue, 8 Oct 2019 09:06:24 -0700
> Jens Alfke  wrote:
>
> > I think the idea of a semi-official ?SQLite++? has been floated here
> > before
>
> OK, but it needs a better name. --
>

SQLiteXTD

   --
  --
 --Ö¿Ö--
K e V i N
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[Doug]

I like SQLoaded!
Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Jose Isaias Cabrera
> Sent: Tuesday, October 08, 2019 12:15 PM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Date time input
> 
> 
> James K. Lowden, on Tuesday, October 8, 2019 02:39 PM, wrote...
> >
> > On Tue, 8 Oct 2019 09:06:24 -0700
> > Jens Alfke, on
> >
> > > I think the idea of a semi-official ?SQLite++? has been
> floated here
> > > before
> >
> > OK, but it needs a better name.  What better place than here to
> debate
> > that?  ;-)
> 
> SQLiteNoMore
> 
> josé
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[Jose Isaias Cabrera]

James K. Lowden, on Tuesday, October 8, 2019 02:39 PM, wrote...
>
> On Tue, 8 Oct 2019 09:06:24 -0700
> Jens Alfke, on
>
> > I think the idea of a semi-official ?SQLite++? has been floated here
> > before
>
> OK, but it needs a better name.  What better place than here to debate
> that?  ;-)

SQLiteNoMore

josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[Simon Slavin]

On 8 Oct 2019, at 7:39pm, James K. Lowden  wrote:

> Simon Slavin  wrote:
> 
>> Converting data to and from a convenient storage format is not the
>> job of a DBMS.
> 
> While I have no quarrel with your specific point about date strings,
> this particular statement is too broad. Arguably, data conversion is
> an inherent, central part of what the DBMS does. Unless, that is,
> you're adept at interpreting IEEE floating point as 1s and 0s.

You're right.  What I meant was something more like "Converting data to and 
from your presentation format …".

On the subject of a name for the heavy version of SQLite, I'd suggest SQLThicc, 
but the 'thicc' meme will have gone away in a few years.  Actually, since it 
won't be supported that long, it's not such a bad name.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[James K. Lowden]

On Mon, 7 Oct 2019 18:17:14 +0100
Simon Slavin  wrote:

> Converting data to and from a convenient storage format is not the
> job of a DBMS.

While I have no quarrel with your specific point about date strings,
this particular statement is too broad.  Arguably, data conversion is
an inherent, central part of what the DBMS does.  Unless, that is,
you're adept at interpreting IEEE floating point as 1s and 0s.

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[James K. Lowden]

On Tue, 8 Oct 2019 09:06:24 -0700
Jens Alfke  wrote:

> I think the idea of a semi-official ?SQLite++? has been floated here
> before

OK, but it needs a better name.  What better place than here to debate
that?  ;-)  

What the opposite of "Lite"?  I don't know.  It's like asking for the
opposite of "organic" milk.  What we have is "milk" and "organic
milk".  In a just and rational world, we'd have "milk" and "industrial
milk".  Milk is, after all, organic to start with.  

Moving on...

We can't us be fat-shaming and all, calling it "SQLfat" or "SQLobese"
or somesuch. "SQLoaded" seems intoxicated.  "SQLplus" isn't accurate;
it's not more than SQL.  

IMO something fanciful is called for.  I nominate "SQLippo".  After the
hippopotamus, not lipposuction, but it's mnemonic either way.  

Mud, mud, glorious mud!  

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[Jose Isaias Cabrera]

Jens Alfke, on Tuesday, October 8, 2019 12:06 PM, wrote...
>
>
> > On Oct 8, 2019, at 5:34 AM, Jose Isaias Cabrera, on
> >
> > No, that is not what I was trying to say or ask.  Not even close. What I was
> trying to say, and most of you missed it was, that if I give date a date 
> format,
> and I also provide the format of how that date is to be understood, ie
>
> Sorry for misunderstanding. But I think this goes against SQLite’s design 
> goal of
> simplicity. Date-time formatting is complicated, so this would add measurably 
> to
> the library’s footprint*, which would be a problem for using it in embedded
> systems, which would require yet another compile-time configuration flag to
> enable/disable it, which would complicate testing... etc.

I agree.

> I think the idea of a semi-official ”SQLite++” has been floated here before: a
> distro with lots of extensions and a more powerful CLI. I like that idea.

That would be something!

josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[Jens Alfke]

> On Oct 8, 2019, at 5:34 AM, Jose Isaias Cabrera  wrote:
> 
> No, that is not what I was trying to say or ask.  Not even close. What I was 
> trying to say, and most of you missed it was, that if I give date a date 
> format, and I also provide the format of how that date is to be understood, 
> ie

Sorry for misunderstanding. But I think this goes against SQLite’s design goal 
of simplicity. Date-time formatting is complicated, so this would add 
measurably to the library’s footprint*, which would be a problem for using it 
in embedded systems, which would require yet another compile-time configuration 
flag to enable/disable it, which would complicate testing... etc.

I think the idea of a semi-official ”SQLite++” has been floated here before: a 
distro with lots of extensions and a more powerful CLI. I like that idea.

—Jens

* Yes, strptime/strftime are in the standard library. But in an embedded system 
that library is statically linked into your binary (there is no OS), so if 
those functions are not dead-stripped, your footprint goes up.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[Jose Isaias Cabrera]

Jens Alfke, on Monday, October 7, 2019 09:18 PM, wrote...
[clip]
> I swear, half the questions on this list build down to "Why doesn't
> SQLite act like MS Access?" If you need all the bells and whistles of 
> formatting
> input and output, then use a fancy DBMS application. SQLite is for embedded 
> use
> _inside_ applications.

No, that is not what I was trying to say or ask.  Not even close. What I was 
trying to say, and most of you missed it was, that if I give date a date 
format, and I also provide the format of how that date is to be understood, ie.

date('5/22/2019','m/d/')

where the date is the first entry, '5/22/2019', and the format is the second 
entry, 'm/d/', that SQLite could take that set of data and easily convert 
and return the ISO date I want.  Yes, I know I can write that outside the code, 
or inside in SQL, but "it would be nice to have this."  Thanks for all the 
responses.

josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[Keith Medcalf]

>Because that's locale-dependent. Some countries, like the US, use
>month/day/year; most other countries use day/month/year. 

Maybe.  Canada supposedly uses the day/month/year format, or so I suddenly 
became aware of in 1998 when I was in my mid 30's.  Prior to that day it was 
m/d/y.  Then again, ever since I was 12 it was always y-m-d.  And the hours 
always went from 00 to 23.

>To interpret such a date string, SQLite has to know what country's 
>customs to use. And that is a pretty significant problem, since:
>
>- Different operating systems communicate locale info in completely
>  different ways
>- The locale settings may not be applied at the layer of the OS where
>  SQLite is running (example: Android only very recently started setting
>  the C-level locale to match the GUI locale.)
>- The current locale may not match the locale from which the date string
>   originates

Not to mention that whoever you have appointed as the "authority" for "locale" 
probably has no bloody clue.  Plus of course if I get on an airplane and fly to 
Japan the locale is completely different from if I hop on a plane and fly to 
Amsterdam, and still different again if I fly to Miami.  No date entered in any 
one of those locale's will be parseable in any other.

In other words a date format of -MM-DD HH:MM:SS.S TZ can always be 
unambiguosly parsed.  The others only perhaps maybe sometimes occassionally.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume. 



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[Jens Alfke]

> On Oct 7, 2019, at 6:17 AM, Jose Isaias Cabrera  wrote:
> 
> I have to ask this question:  Why is it that the date function does not take 
> a '4/5/2019' and returns '2019-04-05'? 

Because that's locale-dependent. Some countries, like the US, use 
month/day/year; most other countries use day/month/year. To interpret such a 
date string, SQLite has to know what country's customs to use. And that is a 
pretty significant problem, since:

- Different operating systems communicate locale info in completely different 
ways
- The locale settings may not be applied at the layer of the OS where SQLite is 
running (example: Android only very recently started setting the C-level locale 
to match the GUI locale.)
- The current locale may not match the locale from which the date string 
originates

A database should not have to care about locales. It's supposed to process data 
in globally-consistent ways that don't depend on settings like that.

I swear, half the questions on this list build down to "Why doesn't 
SQLite act like MS Access?" If you need all the bells and whistles of 
formatting input and output, then use a fancy DBMS application. SQLite is for 
embedded use _inside_ applications.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[Tim Streater]

On 07 Oct 2019, at 17:19, Keith Medcalf  wrote:

> On Monday, 7 October, 2019 07:17, Jose Isaias Cabrera 
> wrote:
>
>>I have to ask this question:  Why is it that the date function does not
>>take a '4/5/2019' and returns '2019-04-05'?  
>
> Because it does not.  Humans read things in big endian order.

No, we read them in order of importance. If someone asks what the date is, the 
most useful answer is the day number of the month (not, for example, the year). 
Equally, if asomeone asks what time of day it is, the most useful answer is the 
hour (not, for example, the second). If someone asked me the date and time, I'd 
hardly respond "It's 2019 and its 27 seconds past." Wouldn't be that useful, 
now would it. So the most important item if first, then the others in 
decreasing order of importance.


-- 
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[Donald Griggs]

Hi, Jose,

Re: Wishing out loud.
Maybe an implementation of sscanf() would be more useful generally.

As to your date parsing problem, if you really insist on doing it in sql,
you may already know how to accomplish it with something like the UPDATE
below:


.mode column
.headers on

create table t1 (dt, dtIso); -- I'm assuming you meant input (dt) to be in
m-d- format (and not d-m-)

INSERT INTO t1 VALUES
   ('2/1/2017',   NULL),
   ('2/19/2019',  NULL),
   ('12/5/1955',  NULL),
   ('12/13/2018', NULL)
  ;

UPDATE t1 SET dtISO = printf('%04d-%02d-%02d',
   substr(dt, -4, 4),
   CAST(dt AS INTEGER),
   CAST ( replace(substr(dt, 3), '/', ' ')  AS INTEGER)
   );

SELECT * FROM t1;
-- output, using sqlite3.exe version 3.29.0, was:

dt  dtIso
--  --
4/5/20192019-04-05
2/19/2019   2019-02-19
12/5/1955   1955-12-05
12/13/2018  2018-12-13

You can then check for a valid date with something like:
SELECT  date('2019-12-32') isnull;
but if it's user input, you'd be sanitizing your inputs before they reach
sqlite, I should hope.

Donald



>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[Simon Slavin]

On 7 Oct 2019, at 2:17pm, Jose Isaias Cabrera  wrote:

> I have to ask this question:  Why is it that the date function does not take 
> a '4/5/2019' and returns '2019-04-05'?

Those are human formats.  SQLite is a database management system.  Its job is 
to store data and allow it to be recalled in convenient ways.

Converting data to and from a convenient storage format is not the job of a 
DBMS.  If you want a software library which does date format conversion, you 
could go look for a software library which does date format conversion.  One 
which can be called from your preferred programming language.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[Keith Medcalf]

On Monday, 7 October, 2019 07:17, Jose Isaias Cabrera  
wrote:

>I have to ask this question:  Why is it that the date function does not
>take a '4/5/2019' and returns '2019-04-05'?  

Because it does not.  Humans read things in big endian order.  For example, in 
the common base 10 system used everywhere in this solar system, the left most 
position contains the biggest thing, with the bigness represented by each digit 
decreasing by a factor of 10 as you travel from left to right.  When you are 
talking about datetime, the biggest thing is the year, then the month, then the 
day, then the hour, then the minute, then the seconds and fractions thereof.  
Therefore, having datetimes in "parts are in descending order of bigness" is 
rational, consistent, cannot be misinterpreted, and requires no external 
rosetta stone for interpretation of the various parts.

How confusing would it be if one million four hundred thirty seven thousand six 
hundred and forty two were written as 4371426?

>This may have been asked
>before, and the answer may be some where in the internet, but, I could
>not find it.  The other thing is that it would be nice to have date take
>something like this,

>date('2/15/2019','m/d/')

This is exactly why.  Because the format is ambiguous and requires the input of 
an external rosetta stone key for decoding, such formats are not used.

>and return

>2019-02-15
>
>so I don't have to deal with the changes. :-)  Just wishing out-loud...

You can always write an extension function that translates between arbitrary 
datetime or number formats into standardized format, or use the built-in 
capabilities of your programming language standard library or Operating System 
to do that before you pass the data to SQLite3 in the format that it requires.  
Just as SQLite3 does not recognize the string 4371426 as a valid representing 
of the number 1437642, it does not recognize that 4/5/6789 is 6789-04-05 (or is 
that 6789-05-04, or is it 04-6789-05 in the Alpha Centauri calendar?).

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume. 



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[Roman Fleysher]

I had to deal with a similar conversion.

I think the answer to why date() does not take other formats is simple: SQLite 
is minimalistic. This string processing can be done outside SQLite library.

Minimalism of SQLite is one of the criteria for what gets implemented. It has 
few mathematical operations, few string manipulations, subset of SQL, etc. 
Sqlite developers are very good at choosing which few to implement to give 
maximum of versatility. Every time I notice such decision I get more impressed. 
I hope you will experience such joy as well. 

 


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Jose Isaias Cabrera [jic...@outlook.com]
Sent: Monday, October 07, 2019 9:17 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Date time input

Greetings.

I have to ask this question:  Why is it that the date function does not take a 
'4/5/2019' and returns '2019-04-05'?  This may have been asked before, and the 
answer may be some where in the internet, but, I could not find it.  The other 
thing is that it would be nice to have date take something like this,

date('2/15/2019','m/d/')

and return

2019-02-15

so I don't have to deal with the changes. :-)  Just wishing out-loud...  Thanks.

josé


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Cfa23548f95114997012508d74b28b25c%7C04c70eb48f2648079934e02e89266ad0%7C1%7C1%7C637060510468196612sdata=kxiXOugWi%2FGEmGs%2FKi7FTyK4xpipJeCmK0EBv9qkbFc%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Date time input

[Jose Isaias Cabrera]

Greetings.

I have to ask this question:  Why is it that the date function does not take a 
'4/5/2019' and returns '2019-04-05'?  This may have been asked before, and the 
answer may be some where in the internet, but, I could not find it.  The other 
thing is that it would be nice to have date take something like this,

date('2/15/2019','m/d/')

and return

2019-02-15

so I don't have to deal with the changes. :-)  Just wishing out-loud...  Thanks.

josé


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users