Re: [sqlite] How to convert a datetime column to a date?

[Tim Streater]

On 22 Mar 2018, at 14:05, Ron Watkins  wrote:

> The file that I use ".import" on contains records like this:

> '2018-03-22 07:01:01'|2533268

> I had assumed the value was being treated as a datetime, but it looks like it
> may be treated as a string?
>
> Im a bit confused because the column definition sais "datetime", not
> "varchar", so if it's being treated as a string what can I do to fix it to be
> treated as a actual datetime datatype?

SQLite has no DATETIME or BOOLEAN datatype, see:



Personally I store all date/times as seconds since the epoch; it simplifies 
life a lot.



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

Re: [sqlite] How to convert a datetime column to a date?

[Peter Da Silva]

On 3/22/18, 9:43 AM, "sqlite-users on behalf of R Smith" 
 wrote:
> On 2018/03/22 4:22 PM, Peter Da Silva wrote:
> > Don't actually need to convert it to datettime if it's already in the right 
> > format, do you, or does datetime() do some grooming the source needs?
>  
> Yes indeed, it's down to "vetting" and in no way needed for the  conversion. 
> If datetime(x) understand x and doesn't produce any NULLs,  then x is a valid 
> ISO8601 date (or valid-enough for SQLite) and all is fine.

Sure, but should you do that there? Because it won't tell you you had bad data, 
it'll just lose it.
 

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

Re: [sqlite] How to convert a datetime column to a date?

[R Smith]

On 2018/03/22 4:22 PM, Peter Da Silva wrote:

Don't actually need to convert it to datettime if it's already in the right 
format, do you, or does datetime() do some grooming the source needs?


Yes indeed, it's down to "vetting" and in no way needed for the 
conversion. If datetime(x) understand x and doesn't produce any NULLs, 
then x is a valid ISO8601 date (or valid-enough for SQLite) and all is fine.


Vetting can however be achieved more simply by a query that doesn't 
alter the data, to allow research into the origin data and finding the 
cause.


I never trust the program making the data to avoid the obvious stray 
character, unneeded quotes, and also things like a date showing leap 
seconds (which might be understood by the target system) or an invalid 
day for a month etc. The OP may however know/trust his source data. I'm 
just overly paranoid. :)




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

Re: [sqlite] How to convert a datetime column to a date?

[Donald Griggs]

Hello, Ron,

Regarding just these two sentences:

I had assumed the value was being treated as a datetime, but it
looks like it may be treated as a string?
I'm a bit confused because the column definition sais "datetime",
not "varchar", ...

The info on this page is very important, particularly section 2.2:
 https://www.sqlite.org/datatype3.html


Sqlite does not use a specific "datetime" type.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to convert a datetime column to a date?

[Ron Watkins]

Thanks, I was able to patch the table by removing the single tick marks using 
the trim and the group by query now works as expected.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Thursday, March 22, 2018 7:23 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] How to convert a datetime column to a date?


On 2018/03/22 4:19 PM, David Raymond wrote:
> Use datetime there instead of date or you'll lose the time part of it.
>
> UPDATE foo SET dttm = datetime(trim(dttm,));

Absolutely, thanks for catching that - I kind of assumed he meant to only have 
the date part, but that is of course in the final query and not in the data.

Final query should then shorten to the actual date part to be:

SELECT date(dttm) AS dt, max(i) AS max_i FROM foo GROUP BY date(dttm) ORDER BY 
1;



>
>
> http://www.sqlite.org/datatype3.html
>
>
> -Original Message-
> From: sqlite-users 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R 
> Smith
> Sent: Thursday, March 22, 2018 10:12 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] How to convert a datetime column to a date?
>
> Ok, that tells us exactly what we needed to know, and Peter was right, 
> those quotes are in the actual data, and shouldn't be.
>
> Try this query please:
>
> UPDATE foo SET dttm = date(trim(dttm,));  -- That's 4 single 
> quotes in a row, which is a single quote, escaped with another single 
> quote, inside two single quotes.
>
> Followed by your original:
>
> SELECT dttm, max(i) AS max_i FROM foo GROUP BY dttm ORDER BY dttm;
>
>
> ___
> 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

___
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] How to convert a datetime column to a date?

[Peter Da Silva]

On 3/22/18, 9:22 AM, "sqlite-users on behalf of Ron Watkins" 
 
wrote:
> Is there an easy way to  "fix" the data already in the table?

The string trim trick David and Ryan suggested would work. To avoid banging on 
rows already converted, maybe:

UPDATE foo SET dttm = trim(dttm,) WHERE dttm like '''%''';

Note that's 4 single quotes in the first string, and two sets of three single 
quotes in the second.

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

Re: [sqlite] How to convert a datetime column to a date?

[R Smith]

On 2018/03/22 4:19 PM, David Raymond wrote:

Use datetime there instead of date or you'll lose the time part of it.

UPDATE foo SET dttm = datetime(trim(dttm,));


Absolutely, thanks for catching that - I kind of assumed he meant to 
only have the date part, but that is of course in the final query and 
not in the data.


Final query should then shorten to the actual date part to be:

SELECT date(dttm) AS dt, max(i) AS max_i FROM foo GROUP BY date(dttm) ORDER BY 
1;






http://www.sqlite.org/datatype3.html


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Thursday, March 22, 2018 10:12 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] How to convert a datetime column to a date?

Ok, that tells us exactly what we needed to know, and Peter was right,
those quotes are in the actual data, and shouldn't be.

Try this query please:

UPDATE foo SET dttm = date(trim(dttm,));  -- That's 4 single quotes
in a row, which is a single quote, escaped with another single quote,
inside two single quotes.

Followed by your original:

SELECT dttm, max(i) AS max_i FROM foo GROUP BY dttm ORDER BY dttm;


___
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


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

Re: [sqlite] How to convert a datetime column to a date?

[Peter Da Silva]

Don't actually need to convert it to datettime if it's already in the right 
format, do you, or does datetime() do some grooming the source needs?

On 3/22/18, 9:19 AM, "sqlite-users on behalf of David Raymond" 
 wrote:

Use datetime there instead of date or you'll lose the time part of it.

UPDATE foo SET dttm = datetime(trim(dttm,));


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

Re: [sqlite] How to convert a datetime column to a date?

[Ron Watkins]

Is there an easy way to  "fix" the data already in the table?


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Peter Da Silva
Sent: Thursday, March 22, 2018 7:09 AM
To: SQLite mailing list
Subject: Re: [sqlite] How to convert a datetime column to a date?

SQLITE3 datetime fields are actually text.

Fix the file that you're importing so it doesn't have quotes around the date. 
It looks like you're using some kind of modified CSV exporter to create the 
file.

On 3/22/18, 9:06 AM, "sqlite-users on behalf of Ron Watkins" 
 
wrote:

The file that I use ".import" on contains records like this:

 

'2018-03-22 07:01:01'|2533268

 

I had assumed the value was being treated as a datetime, but it looks like 
it may be treated as a string?

Im a bit confused because the column definition sais "datetime", not 
"varchar", so if it's being treated as a string what can I do to fix it to be 
treated as a actual datetime datatype?

 

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Peter Da Silva
Sent: Thursday, March 22, 2018 6:49 AM
To: SQLite mailing list
Subject: Re: [sqlite] How to convert a datetime column to a date?

 

1. It looks like you have superfluous quotes around the dates. That's 
probably your issue.

 

2. Try "select distinct dttm from foo LIMIT 10;"

 

On 3/22/18, 8:47 AM, "sqlite-users on behalf of Ron Watkins" < 

 sqlite-users-boun...@mailinglists.sqlite.org on behalf of rwa...@gmail.com> 
wrote:

sqlite> select distinct dttm from foo;



…  (lots of records)



'2018-03-22 06:25:01'



'2018-03-22 06:26:01'



'2018-03-22 06:27:01'



'2018-03-22 06:28:01'



'2018-03-22 06:29:01'



'2018-03-22 06:30:01'



'2018-03-22 06:31:01'



'2018-03-22 06:32:02'



'2018-03-22 06:33:01'



'2018-03-22 06:34:01'



'2018-03-22 06:35:01'



'2018-03-22 06:36:01'



'2018-03-22 06:37:01'



'2018-03-22 06:38:01'



'2018-03-22 06:39:01'



'2018-03-22 06:40:01'



'2018-03-22 06:41:01'



'2018-03-22 06:42:01'



'2018-03-22 06:43:01'



'2018-03-22 06:44:01'



sqlite>



 



BTW, is there some equilivant to “select top 10 * from foo;” style to 
reduce the number of records? I tried this “top 10” but it’s apparently  not 
supported.



 



-Original Message-

From: sqlite-users [ 
 
mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Peter Da Silva

Sent: Thursday, March 22, 2018 6:33 AM

To: SQLite mailing list

Subject: Re: [sqlite] How to convert a datetime column to a date?



 



It might be helpful to provide some examples of what you have in those 
DATETIME columns.



 



___



sqlite-users mailing list



 <  
mailto:sqlite-users@mailinglists.sqlite.org>  
 
sqlite-users@mailinglists.sqlite.org



 < 
 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>  
 
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



 

___

sqlite-users mailing list

  

Re: [sqlite] How to convert a datetime column to a date?

[David Raymond]

Use datetime there instead of date or you'll lose the time part of it.

UPDATE foo SET dttm = datetime(trim(dttm,));


http://www.sqlite.org/datatype3.html


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Thursday, March 22, 2018 10:12 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] How to convert a datetime column to a date?

Ok, that tells us exactly what we needed to know, and Peter was right, 
those quotes are in the actual data, and shouldn't be.

Try this query please:

UPDATE foo SET dttm = date(trim(dttm,));  -- That's 4 single quotes 
in a row, which is a single quote, escaped with another single quote, 
inside two single quotes.

Followed by your original:

SELECT dttm, max(i) AS max_i FROM foo GROUP BY dttm ORDER BY dttm;


___
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] How to convert a datetime column to a date?

[R Smith]

Ok, that tells us exactly what we needed to know, and Peter was right, 
those quotes are in the actual data, and shouldn't be.


Try this query please:

UPDATE foo SET dttm = date(trim(dttm,));  -- That's 4 single quotes 
in a row, which is a single quote, escaped with another single quote, 
inside two single quotes.


Followed by your original:

SELECT dttm, max(i) AS max_i FROM foo GROUP BY dttm ORDER BY dttm;


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

Re: [sqlite] How to convert a datetime column to a date?

[Peter Da Silva]

SQLITE3 datetime fields are actually text.

Fix the file that you're importing so it doesn't have quotes around the date. 
It looks like you're using some kind of modified CSV exporter to create the 
file.

On 3/22/18, 9:06 AM, "sqlite-users on behalf of Ron Watkins" 
 
wrote:

The file that I use ".import" on contains records like this:

 

'2018-03-22 07:01:01'|2533268

 

I had assumed the value was being treated as a datetime, but it looks like 
it may be treated as a string?

Im a bit confused because the column definition sais "datetime", not 
"varchar", so if it's being treated as a string what can I do to fix it to be 
treated as a actual datetime datatype?

 

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Peter Da Silva
Sent: Thursday, March 22, 2018 6:49 AM
To: SQLite mailing list
Subject: Re: [sqlite] How to convert a datetime column to a date?

 

1. It looks like you have superfluous quotes around the dates. That's 
probably your issue.

 

2. Try "select distinct dttm from foo LIMIT 10;"

 

On 3/22/18, 8:47 AM, "sqlite-users on behalf of Ron Watkins" < 

 sqlite-users-boun...@mailinglists.sqlite.org on behalf of rwa...@gmail.com> 
wrote:

sqlite> select distinct dttm from foo;



…  (lots of records)



'2018-03-22 06:25:01'



'2018-03-22 06:26:01'



'2018-03-22 06:27:01'



'2018-03-22 06:28:01'



'2018-03-22 06:29:01'



'2018-03-22 06:30:01'



'2018-03-22 06:31:01'



'2018-03-22 06:32:02'



'2018-03-22 06:33:01'



'2018-03-22 06:34:01'



'2018-03-22 06:35:01'



'2018-03-22 06:36:01'



'2018-03-22 06:37:01'



'2018-03-22 06:38:01'



'2018-03-22 06:39:01'



'2018-03-22 06:40:01'



'2018-03-22 06:41:01'



'2018-03-22 06:42:01'



'2018-03-22 06:43:01'



'2018-03-22 06:44:01'



sqlite>



 



BTW, is there some equilivant to “select top 10 * from foo;” style to 
reduce the number of records? I tried this “top 10” but it’s apparently  not 
supported.



 



-Original Message-

From: sqlite-users [ 
 
mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Peter Da Silva

Sent: Thursday, March 22, 2018 6:33 AM

To: SQLite mailing list

Subject: Re: [sqlite] How to convert a datetime column to a date?



 



It might be helpful to provide some examples of what you have in those 
DATETIME columns.



 



___



sqlite-users mailing list



 <  
mailto:sqlite-users@mailinglists.sqlite.org>  
 
sqlite-users@mailinglists.sqlite.org



 < 
 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>  
 
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



 

___

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

Re: [sqlite] How to convert a datetime column to a date?

[Ron Watkins]

The file that I use ".import" on contains records like this:

 

'2018-03-22 07:01:01'|2533268

 

I had assumed the value was being treated as a datetime, but it looks like it 
may be treated as a string?

Im a bit confused because the column definition sais "datetime", not "varchar", 
so if it's being treated as a string what can I do to fix it to be treated as a 
actual datetime datatype?

 

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Peter Da Silva
Sent: Thursday, March 22, 2018 6:49 AM
To: SQLite mailing list
Subject: Re: [sqlite] How to convert a datetime column to a date?

 

1. It looks like you have superfluous quotes around the dates. That's probably 
your issue.

 

2. Try "select distinct dttm from foo LIMIT 10;"

 

On 3/22/18, 8:47 AM, "sqlite-users on behalf of Ron Watkins" < 

 sqlite-users-boun...@mailinglists.sqlite.org on behalf of rwa...@gmail.com> 
wrote:

sqlite> select distinct dttm from foo;



…  (lots of records)



'2018-03-22 06:25:01'



'2018-03-22 06:26:01'



'2018-03-22 06:27:01'



'2018-03-22 06:28:01'



'2018-03-22 06:29:01'



'2018-03-22 06:30:01'



'2018-03-22 06:31:01'



'2018-03-22 06:32:02'



'2018-03-22 06:33:01'



'2018-03-22 06:34:01'



'2018-03-22 06:35:01'



'2018-03-22 06:36:01'



'2018-03-22 06:37:01'



'2018-03-22 06:38:01'



'2018-03-22 06:39:01'



'2018-03-22 06:40:01'



'2018-03-22 06:41:01'



'2018-03-22 06:42:01'



'2018-03-22 06:43:01'



'2018-03-22 06:44:01'



sqlite>



 



BTW, is there some equilivant to “select top 10 * from foo;” style to 
reduce the number of records? I tried this “top 10” but it’s apparently  not 
supported.



 



-Original Message-

From: sqlite-users [  
mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Peter Da Silva

Sent: Thursday, March 22, 2018 6:33 AM

To: SQLite mailing list

Subject: Re: [sqlite] How to convert a datetime column to a date?



 



It might be helpful to provide some examples of what you have in those 
DATETIME columns.



 



___



sqlite-users mailing list



 <  
mailto:sqlite-users@mailinglists.sqlite.org>  
 
sqlite-users@mailinglists.sqlite.org



 <  
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>  
 
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



 

___

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] How to convert a datetime column to a date?

[Ron Watkins]

sqlite> select '_' || dttm || '_' from foo limit 20;

_'2017-11-08 07:00:01'_

_'2017-11-08 07:00:01'_

_'2017-11-08 07:00:01'_

_'2017-11-08 07:01:01'_

_'2017-11-08 07:01:01'_

_'2017-11-08 07:01:01'_

_'2017-11-08 07:02:01'_

_'2017-11-08 07:02:01'_

_'2017-11-08 07:02:01'_

_'2017-11-08 07:03:01'_

_'2017-11-08 07:03:01'_

_'2017-11-08 07:03:01'_

_'2017-11-08 07:04:01'_

_'2017-11-08 07:04:01'_

_'2017-11-08 07:04:01'_

_'2017-11-08 07:05:01'_

_'2017-11-08 07:05:01'_

_'2017-11-08 07:05:01'_

_'2017-11-08 07:06:01'_

_'2017-11-08 07:06:01'_

 

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Thursday, March 22, 2018 6:48 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] How to convert a datetime column to a date?

 

 

 

On 2018/03/22 3:21 PM, Ron Watkins wrote:

> It doesn't seem to work that way. I have 192330 distinct dttm entries, but I 
> still only get 1 row. It seems as the “date(dttm)” result is either null or 
> the empty string for all records. Not sure why. This works in other 
> databases, so there must be something subtle about the “date()” function or 
> the “dttm” column that im missing here.

> 

>   

> 

> sqlite> select date(dttm) as dt,max(used_kb)/1024.0/1024.0 from foo 

> sqlite> group by dt order by 1;

> 

> |5038.83195495606

> 

> sqlite> select count(distinct dttm) from foo;

> 

> 192330

> 

> sqlite>

 

Could you post the results of:

 

SELECT '_' || dttm || '_' FROM foo LIMIT 20;

 

The underscores are just so I can tell if there are any weird leading/trailing 
spaces in your data.

___

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] How to convert a datetime column to a date?

[Peter Da Silva]

1. It looks like you have superfluous quotes around the dates. That's probably 
your issue.

2. Try "select distinct dttm from foo LIMIT 10;"

On 3/22/18, 8:47 AM, "sqlite-users on behalf of Ron Watkins" 
 
wrote:
sqlite> select distinct dttm from foo;

…  (lots of records)

'2018-03-22 06:25:01'

'2018-03-22 06:26:01'

'2018-03-22 06:27:01'

'2018-03-22 06:28:01'

'2018-03-22 06:29:01'

'2018-03-22 06:30:01'

'2018-03-22 06:31:01'

'2018-03-22 06:32:02'

'2018-03-22 06:33:01'

'2018-03-22 06:34:01'

'2018-03-22 06:35:01'

'2018-03-22 06:36:01'

'2018-03-22 06:37:01'

'2018-03-22 06:38:01'

'2018-03-22 06:39:01'

'2018-03-22 06:40:01'

'2018-03-22 06:41:01'

'2018-03-22 06:42:01'

'2018-03-22 06:43:01'

'2018-03-22 06:44:01'

sqlite>

 

BTW, is there some equilivant to “select top 10 * from foo;” style to 
reduce the number of records? I tried this “top 10” but it’s apparently  not 
supported.

 

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Peter Da Silva
Sent: Thursday, March 22, 2018 6:33 AM
To: SQLite mailing list
Subject: Re: [sqlite] How to convert a datetime column to a date?

 

It might be helpful to provide some examples of what you have in those 
DATETIME columns.

 

___

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


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

Re: [sqlite] How to convert a datetime column to a date?

[R Smith]

On 2018/03/22 3:21 PM, Ron Watkins wrote:

It doesn't seem to work that way. I have 192330 distinct dttm entries, but I 
still only get 1 row. It seems as the “date(dttm)” result is either null or the 
empty string for all records. Not sure why. This works in other databases, so 
there must be something subtle about the “date()” function or the “dttm” column 
that im missing here.

  


sqlite> select date(dttm) as dt,max(used_kb)/1024.0/1024.0 from foo group by dt 
order by 1;

|5038.83195495606

sqlite> select count(distinct dttm) from foo;

192330

sqlite>


Could you post the results of:

SELECT '_' || dttm || '_' FROM foo LIMIT 20;

The underscores are just so I can tell if there are any weird 
leading/trailing spaces in your data.

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

Re: [sqlite] How to convert a datetime column to a date?

[Ron Watkins]

The table defines “dttm” as a “datetime” datatype.

 

sqlite> select distinct dttm from foo;

…  (lots of records)

'2018-03-22 06:25:01'

'2018-03-22 06:26:01'

'2018-03-22 06:27:01'

'2018-03-22 06:28:01'

'2018-03-22 06:29:01'

'2018-03-22 06:30:01'

'2018-03-22 06:31:01'

'2018-03-22 06:32:02'

'2018-03-22 06:33:01'

'2018-03-22 06:34:01'

'2018-03-22 06:35:01'

'2018-03-22 06:36:01'

'2018-03-22 06:37:01'

'2018-03-22 06:38:01'

'2018-03-22 06:39:01'

'2018-03-22 06:40:01'

'2018-03-22 06:41:01'

'2018-03-22 06:42:01'

'2018-03-22 06:43:01'

'2018-03-22 06:44:01'

sqlite>

 

BTW, is there some equilivant to “select top 10 * from foo;” style to reduce 
the number of records? I tried this “top 10” but it’s apparently  not supported.

 

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Peter Da Silva
Sent: Thursday, March 22, 2018 6:33 AM
To: SQLite mailing list
Subject: Re: [sqlite] How to convert a datetime column to a date?

 

It might be helpful to provide some examples of what you have in those DATETIME 
columns.

 

___

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] How to convert a datetime column to a date?

[Peter Da Silva]

It might be helpful to provide some examples of what you have in those DATETIME 
columns.

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

Re: [sqlite] How to convert a datetime column to a date?

[Ron Watkins]

It doesn't seem to work that way. I have 192330 distinct dttm entries, but I 
still only get 1 row. It seems as the “date(dttm)” result is either null or the 
empty string for all records. Not sure why. This works in other databases, so 
there must be something subtle about the “date()” function or the “dttm” column 
that im missing here.

 

sqlite> select date(dttm) as dt,max(used_kb)/1024.0/1024.0 from foo group by dt 
order by 1;

|5038.83195495606

sqlite> select count(distinct dttm) from foo;

192330

sqlite>

 

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Thursday, March 22, 2018 4:21 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] How to convert a datetime column to a date?

 

 

On 2018/03/21 5:30 PM, Ron Watkins wrote:

> I have a table which contains a datetime column:

> 

> 

> 

> table|foo|foo|2|CREATE TABLE foo (

> 

>  dttmdatetimenot null

> 

>  i int not null

> 

> )

> 

> 

> 

> I want to select out the max(i) value for each day where there are 

> multiple records per day.

> 

> 

> 

> select date(dttm) dt,max(i) from foo group by dt order by 1;

> 

> 

> 

> However, it’s returning only 1 row, with no date column shown. How can 

> I get an actual “date” listed in the first column, and also get 1 row 

> per “date” value.

 

I fear I might be missing something obvious, but wouldn't this just do the job?:

SELECT date(dttm) AS dt, max(i)

  FROM foo

 GROUP BY date(dttm)

 

If you still get only 1 row, it means there is only 1 date value in all the 
rows in your table.

 

 

___

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] How to convert a datetime column to a date?

[David Raymond]

Your query is fine. If you're only getting 1 row, then there's only 1 date in 
your data. If you're not getting a date column, then there's something 
drastically wrong with whatever you're using, as you have it right there in 
your query. Are your datetimes not stored correctly perhaps?

SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table foo (dttm datetime not null, i int not null);
--EQP-- 0,0,0,SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?)

sqlite> insert into foo values
   ...> ('2018-03-22 07:00:00', 12),
   ...> ('2018-02-01 10:00:00', 1),
   ...> ('2018-03-22 05:00:00', 20),
   ...> ('2018-02-01 12:00:00', 2);

sqlite> select date(dttm) as dt, max(i) from foo group by dt order by 1;
--EQP-- 0,0,0,SCAN TABLE foo
--EQP-- 0,0,0,USE TEMP B-TREE FOR GROUP BY
dt|max(i)
2018-02-01|2
2018-03-22|20


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Ron Watkins
Sent: Wednesday, March 21, 2018 11:30 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] How to convert a datetime column to a date?

I have a table which contains a datetime column:



table|foo|foo|2|CREATE TABLE foo (

dttmdatetimenot null

i int not null

)



I want to select out the max(i) value for each day where there are multiple
records per day.



select date(dttm) dt,max(i) from foo group by dt order by 1;



However, it’s returning only 1 row, with no date column shown. How can I
get an actual “date” listed in the first column, and also get 1 row per
“date” value.



|5283598256
___
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] How to convert a datetime column to a date?

[R Smith]

On 2018/03/21 5:30 PM, Ron Watkins wrote:

I have a table which contains a datetime column:



table|foo|foo|2|CREATE TABLE foo (

 dttmdatetimenot null

 i int not null

)



I want to select out the max(i) value for each day where there are multiple
records per day.



select date(dttm) dt,max(i) from foo group by dt order by 1;



However, it’s returning only 1 row, with no date column shown. How can I
get an actual “date” listed in the first column, and also get 1 row per
“date” value.


I fear I might be missing something obvious, but wouldn't this just do 
the job?:

SELECT date(dttm) AS dt, max(i)
  FROM foo
 GROUP BY date(dttm)

If you still get only 1 row, it means there is only 1 date value in all 
the rows in your table.



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

Re: [sqlite] How to convert a datetime column to a date?

[Paul Sanderson]

how about

select date(dttm) dt,max(i) from foo group by date(dttm) order by 1;


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 21 March 2018 at 15:30, Ron Watkins  wrote:

> I have a table which contains a datetime column:
>
>
>
> table|foo|foo|2|CREATE TABLE foo (
>
> dttmdatetimenot null
>
> i int not null
>
> )
>
>
>
> I want to select out the max(i) value for each day where there are multiple
> records per day.
>
>
>
> select date(dttm) dt,max(i) from foo group by dt order by 1;
>
>
>
> However, it’s returning only 1 row, with no date column shown. How can I
> get an actual “date” listed in the first column, and also get 1 row per
> “date” value.
>
>
>
> |5283598256
> ___
> 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