Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??
The "date" is probably a timedate, and eventhough you see it as the only-the-date, the time makes it unique. Try to put also a groupby clause:
select distinct cast(DATE as date) from myschema.mytable GROUP BY DATE

untested.

Edit: somewhat tested on https://www.jdoodle.com/execute-sql-online/

create table foo (IDENTITY INT, LoginTime DATETIME); INSERT INTO foo VALUES( 1, '2019-09-29 17:00:48.000' ); INSERT INTO foo VALUES( 2, '2019-09-29 17:29:02.000' ); INSERT INTO foo VALUES( 3, '2019-09-30 10:22:46.000' ); INSERT INTO foo VALUES( 4, '2019-09-30 11:16:23.000' ); --Select unique SELECT distinct cast(LoginTime as date) FROM foo GROUP BY LoginTime;

returns: 2019 (just the year, strange)

EDIT 2: Ok, tried sqlite from Perl and SQLite does not have DATE... and returns the same "2019" as the online version. But it DOES have strftime and that made it work

#!/usr/bin/perl use strict; use warnings; use DBI; my $dbfile = "sample.db"; my $dsn = "dbi:SQLite:dbname=$dbfile"; my $user = "x"; my $password = "x"; my $dbh = DBI->connect($dsn, $user, $password, { PrintError => 0, RaiseError => 1, AutoCommit => 1, FetchHashKeyName => 'NAME_lc', }); if(-z $dbfile){ my $sql = <<'END_SQL'; CREATE TABLE foo ( id INTEGER PRIMARY KEY, LoginTime DATE ) END_SQL $dbh->do($sql); my @DATES = ('2019-09-29 17:00:48.000', '2019-09-29 17:29:02.000', ' +2019-09-30 10:22:46.000', '2019-09-30 11:16:23.000'); for(my $i=0;$i<=$#DATES;$i++){ $dbh->do('INSERT INTO foo (LoginTime) VALUES (?)', undef,$DATES[$i]); } } # This is what I thought would work #my $sql = 'select distinct cast(LoginTime as date) from foo GROUP BY +LoginTime'; # this actually works in SQLite (strftime) my $sql = 'select distinct strftime("%d-%m-%Y",LoginTime) from foo'; my $sth = $dbh->prepare($sql); $sth->execute(); while (my @row = $sth->fetchrow_array) { print "[@row]\n"; } $dbh->disconnect;

outputs:

[29-09-2019] [30-09-2019]

Ok, after reading @Erix's comment, he's right. And Anonymous Monk (too bad for the points) pointed the error out, and it also occurs with SQLite. Data::Dumper shows the following after injecting 29th then 30th:

$VAR1 = [ [ '30-09-2019' ], $VAR1->[0] ];

So all old values become a reference to the first entry of itself. So if you inject n fetchrow_arrayref() then you get n times the last pushed entry.
So to copy an arrayref: [@$ref] to get an AoA in @all_rows.

$sth->execute(); my @all_rows; while ( my $ref = $sth->fetchrow_arrayref()) { # Needs to be de-referenced (Anonymous monk's fix) push(@all_rows, [@{$ref}]); } foreach my $row_ref (@all_rows) { print "$row_ref->[0]\n"; }

And my first example worked because I used fetchrow_array() instead of fetchrow_arrayref() and went around the referencing problem (but references are faster in general, not by much, so keep using fetchrow_arrayref)


In reply to Re: DBI: select distinct returns same values by FreeBeerReekingMonk
in thread DBI: select distinct returns same values by tukusejssirs

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others meditating upon the Monastery: (5)
    As of 2020-11-30 17:07 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      No recent polls found

      Notices?