Quote within a quote postgres query

Dev Shed Forums Sponsor:

February 11th, 2013, 09:55 AM

dwsdad

Registered User

Join Date: Feb 2013

Posts: 4

Time spent in forums: 17 m 25 sec
Reputation 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?

February 11th, 2013, 10:31 AM

FishMonger

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Apr 2009

Posts: 1,652

Time spent in forums: 1 Month 2 Weeks 2 Days 5 h 32 m 21 sec
Reputation Power: 1170

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";
}

February 11th, 2013, 11:50 AM

keath

!~ /m$/

Join Date: May 2004

Location: Reno, NV

Posts: 4,087

Time spent in forums: 2 Weeks 4 Days 6 h 59 m 11 sec
Reputation Power: 1809

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.

February 11th, 2013, 01:23 PM

dwsdad

Registered User

Join Date: Feb 2013

Posts: 4

Time spent in forums: 17 m 25 sec
Reputation Power: 0

Quote:

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.

February 11th, 2013, 02:18 PM

dwsdad

Registered User

Join Date: Feb 2013

Posts: 4

Time spent in forums: 17 m 25 sec
Reputation Power: 0

Quote:

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!

February 11th, 2013, 10:05 PM

keath

!~ /m$/

Join Date: May 2004

Location: Reno, NV

Posts: 4,087

Time spent in forums: 2 Weeks 4 Days 6 h 59 m 11 sec
Reputation Power: 1809

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

Quote:

20130211 07:58:29

February 12th, 2013, 07:50 AM

dwsdad

Registered User

Join Date: Feb 2013

Posts: 4

Time spent in forums: 17 m 25 sec
Reputation Power: 0

Quote:

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.

Quote within a quote postgres query

Developer Shed Advertisers and Affiliates