#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0

    Quote within a quote postgres query


    Sorry, guys, I'm new to the Forum and I'm not sure where exactly to post this question, so here goes:

    I have a Perl script that runs a query against a Postgres database. The script itself works well, but I have a need to convert a time/date field to character in order to change the actual date format. I know that is kinda cryptic, so here's a snippet of what I have:

    my $sth = $dbh->prepare(
    'SELECT
    <time/date field>
    from <some_table_name>');

    The output of which is 2013-02-13 12:00:00

    What I need to do is change that to 20130213 12:00:00, so I've tried:

    my $sth = $dbh->prepare(
    'SELECT
    to_char(<time/date field>, 'YYYYMMDD HH:MI:SS')
    from <some_table_name>');

    which of course generates an error. So then I tried:

    my $sth = $dbh->prepare(
    'SELECT
    to_char(<time/date field>, \'YYYYMMDD HH:MI:SS'\)
    from <some_table_name>');

    which drops the output all together. So how can I change that format?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,923
    Rep Power
    1225
    I don't use Postgres so I can't give you the proper postgres syntax to do the formating in the select statement.

    I'd simply use a regex or the tr operator to strip out the '-' hyphens.

    Code:
    while (my ($datetime) = $sth->fetchrow_array) {
        $datetime =~ s/-//g;
        print "$datetime\n";
    }
  4. #3
  5. !~ /m$/
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    May 2004
    Location
    Reno, NV
    Posts
    4,252
    Rep Power
    1810
    Perl gives you every option for quoting strings.

    Try something like:
    Code:
    my $query = q{SELECT to_char(<time/date field>, 'YYYYMMDD HH:MI:SS') from <some_table_name>};
    
    my $sth = $dbh->prepare($query);
    Last edited by keath; February 11th, 2013 at 11:52 AM.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by keath
    Perl gives you every option for quoting strings.

    Try something like:
    Code:
    my $query = q{SELECT to_char(<time/date field>, 'YYYYMMDD HH:MI:SS') from <some_table_name>};
    
    my $sth = $dbh->prepare($query);
    Thanks for the suggestion, but it's still not returning the field.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by FishMonger
    I don't use Postgres so I can't give you the proper postgres syntax to do the formating in the select statement.

    I'd simply use a regex or the tr operator to strip out the '-' hyphens.

    Code:
    while (my ($datetime) = $sth->fetchrow_array) {
        $datetime =~ s/-//g;
        print "$datetime\n";
    }
    This works like a charm! Thanks!
  10. #6
  11. !~ /m$/
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    May 2004
    Location
    Reno, NV
    Posts
    4,252
    Rep Power
    1810
    Glad you got the result you wanted.

    Wanted to try this out when I got home though (for science), and the query seems to work just fine here.

    Code:
    #!/usr/bin/perl
    use strict;
    use warnings;
    
    use DBI;
    
    my $dbh = DBI->connect("dbi:Pg:dbname=test","keath","") or die "Unable to connect: $DBI::errstr";
    
    my $query = q{SELECT to_char(created, 'YYYYMMDD HH:MI:SS') from customer where id = 1};
    my ($time) = $dbh->selectrow_array($query);
    print "$time\n";
    result
    20130211 07:58:29
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by keath
    Glad you got the result you wanted.

    Wanted to try this out when I got home though (for science), and the query seems to work just fine here.

    Code:
    #!/usr/bin/perl
    use strict;
    use warnings;
    
    use DBI;
    
    my $dbh = DBI->connect("dbi:Pg:dbname=test","keath","") or die "Unable to connect: $DBI::errstr";
    
    my $query = q{SELECT to_char(created, 'YYYYMMDD HH:MI:SS') from customer where id = 1};
    my ($time) = $dbh->selectrow_array($query);
    print "$time\n";
    result
    I'll give that one a try too. Nothing like trying to learn Perl and Postgres at the same time and under a deadline.

IMN logo majestic logo threadwatch logo seochat tools logo