How to convert a datetime column to a date?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
24 messages Options
12
Reply | Threaded
Open this post in threaded view
|

How to convert a datetime column to a date?

Ron Watkins
I have a table which contains a datetime column:



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

        dttm    datetime        not 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> wrote:

> I have a table which contains a datetime column:
>
>
>
> table|foo|foo|2|CREATE TABLE foo (
>
>         dttm    datetime        not 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert a datetime column to a date?

R Smith-2
In reply to this post by Ron Watkins

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 (
>
>          dttm    datetime        not 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert a datetime column to a date?

David Raymond
In reply to this post by Ron Watkins
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:[hidden email]] On Behalf Of Ron Watkins
Sent: Wednesday, March 21, 2018 11:30 AM
To: [hidden email]
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 (

        dttm    datetime        not 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Hick Gunter
In reply to this post by Ron Watkins
Whatever is in your dttm column (there is no datetime type in sqlite) does not parse as a valid timestring, so date() returns NULL for each and every row, giving just 1 result row.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Ron Watkins
Gesendet: Mittwoch, 21. März 2018 16:30
An: [hidden email]
Betreff: [EXTERNAL] [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 (

        dttm    datetime        not 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert a datetime column to a date?

Ron Watkins-2
In reply to this post by R Smith-2
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:[hidden email]] On Behalf Of R Smith
Sent: Thursday, March 22, 2018 4:21 AM
To: [hidden email]
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 (

>

>          dttm    datetime        not 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

 <mailto:[hidden email]> [hidden email]

 <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert a datetime column to a date?

Ron Watkins-2
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:[hidden email]] 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:[hidden email]> [hidden email]

 <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert a datetime column to a date?

R Smith-2
In reply to this post by Ron Watkins-2


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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert a datetime column to a date?

Peter da Silva
In reply to this post by Ron Watkins-2
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" <[hidden email] on behalf of [hidden email]> 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:[hidden email]] 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:[hidden email]> [hidden email]
   
     <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
   
    _______________________________________________
    sqlite-users mailing list
    [hidden email]
    http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
   

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert a datetime column to a date?

Ron Watkins-2
In reply to this post by R Smith-2
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:[hidden email]] On Behalf Of R Smith
Sent: Thursday, March 22, 2018 6:48 AM
To: [hidden email]
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

 <mailto:[hidden email]> [hidden email]

 <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert a datetime column to a date?

Ron Watkins-2
In reply to this post by Peter da Silva
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:[hidden email]] 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" < <mailto:[hidden email]%20on%20behalf%20of%[hidden email]> [hidden email] on behalf of [hidden email]> 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:[hidden email]> mailto:[hidden email]] 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:[hidden email]> mailto:[hidden email]>  <mailto:[hidden email]> [hidden email]

   

     < <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>  <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

   

    _______________________________________________

    sqlite-users mailing list

     <mailto:[hidden email]> [hidden email]

     <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

   

 

_______________________________________________

sqlite-users mailing list

 <mailto:[hidden email]> [hidden email]

 <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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" <[hidden email] on behalf of [hidden email]> 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:[hidden email]] 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" < <mailto:[hidden email]%20on%20behalf%20of%[hidden email]> [hidden email] on behalf of [hidden email]> 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:[hidden email]> mailto:[hidden email]] 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:[hidden email]> mailto:[hidden email]>  <mailto:[hidden email]> [hidden email]
   
       
   
         < <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>  <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
   
       
   
        _______________________________________________
   
        sqlite-users mailing list
   
         <mailto:[hidden email]> [hidden email]
   
         <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
   
       
   
     
   
    _______________________________________________
   
    sqlite-users mailing list
   
     <mailto:[hidden email]> [hidden email]
   
     <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
   
    _______________________________________________
    sqlite-users mailing list
    [hidden email]
    http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
   

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert a datetime column to a date?

R Smith-2
In reply to this post by Ron Watkins-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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:[hidden email]] On Behalf Of R Smith
Sent: Thursday, March 22, 2018 10:12 AM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert a datetime column to a date?

Ron Watkins-2
In reply to this post by Peter da Silva
Is there an easy way to  "fix" the data already in the table?


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] 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" <[hidden email] on behalf of [hidden email]> 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:[hidden email]] 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" < <mailto:[hidden email]%20on%20behalf%20of%[hidden email]> [hidden email] on behalf of [hidden email]> 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:[hidden email]> mailto:[hidden email]] 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:[hidden email]> mailto:[hidden email]>  <mailto:[hidden email]> [hidden email]
   
       
   
         < <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>  <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
   
       
   
        _______________________________________________
   
        sqlite-users mailing list
   
         <mailto:[hidden email]> [hidden email]
   
         <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
   
       
   
     
   
    _______________________________________________
   
    sqlite-users mailing list
   
     <mailto:[hidden email]> [hidden email]
   
     <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
   
    _______________________________________________
    sqlite-users mailing list
    [hidden email]
    http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
   

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert a datetime column to a date?

Peter da Silva
In reply to this post by David Raymond
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" <[hidden email] on behalf of [hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert a datetime column to a date?

R Smith-2
In reply to this post by David Raymond

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:[hidden email]] On Behalf Of R Smith
> Sent: Thursday, March 22, 2018 10:12 AM
> To: [hidden email]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert a datetime column to a date?

Peter da Silva
In reply to this post by Ron Watkins-2
On 3/22/18, 9:22 AM, "sqlite-users on behalf of Ron Watkins" <[hidden email] on behalf of [hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert a datetime column to a date?

Ron Watkins-2
In reply to this post by R Smith-2
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:[hidden email]] On Behalf Of R Smith
Sent: Thursday, March 22, 2018 7:23 AM
To: [hidden email]
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:[hidden email]] On Behalf Of R
> Smith
> Sent: Thursday, March 22, 2018 10:12 AM
> To: [hidden email]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12