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

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

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

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

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

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)

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

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,

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

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

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

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

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

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

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"

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

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

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

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:

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,

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"

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

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

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

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

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" wrote:

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

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

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

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

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

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

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

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

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

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

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

[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)