I am getting a field of date type from PostgreSQL and an
integer that is a number of days (1 to 3).

select date, num_days from table
$date, $num_days

In my perl script I am getting the current date

#find current date
($mday,$mon,$year,$doy)=(localtime(time))[3..5,7];
$mon ++;
$year +=1900;
$today = "$year-$mon-$mday";

I need to test whether $date from PostgreSQL matches
$today + $num_days.

Can perl extract day of year ($doy) from the Postgres date?

Is there a better way to do this?

Thanks,
Tom Hodges

Search Discussions

  • Frank Way at Dec 3, 2003 at 8:32 pm
    If you are using the Perl Date::Calc module (available from CPAN) you
    should be able to plug the year, month, day fields (that you can fairly
    easily extract from the date returned by PostgreSQL) into the
    Day_of_Year function and get what you want.

    Hope this helps,
    Frank Way


    --- hodges@xprt.net wrote:
    I am getting a field of date type from PostgreSQL and an
    integer that is a number of days (1 to 3).

    select date, num_days from table
    $date, $num_days

    In my perl script I am getting the current date

    #find current date
    ($mday,$mon,$year,$doy)=(localtime(time))[3..5,7];
    $mon ++;
    $year +=1900;
    $today = "$year-$mon-$mday";

    I need to test whether $date from PostgreSQL matches
    $today + $num_days.

    Can perl extract day of year ($doy) from the Postgres date?

    Is there a better way to do this?

    Thanks,
    Tom Hodges

    ---------------------------(end of
    broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to
    majordomo@postgresql.org


    =====
    Frank Way
    E-Mail: fgw_three@yahoo.com

    "Place guards on all the roads and keep the troops
    from running to the rear..." MG John Buford, 1863

    __________________________________
    Do you Yahoo!?
    Free Pop-Up Blocker - Get it now
    http://companion.yahoo.com/
  • SaiHertz And Control Systems at Dec 3, 2003 at 9:29 pm

    hodges@xprt.net wrote:
    I am getting a field of date type from PostgreSQL and an
    integer that is a number of days (1 to 3).

    select date, num_days from table
    $date, $num_days

    In my perl script I am getting the current date

    #find current date
    ($mday,$mon,$year,$doy)=(localtime(time))[3..5,7];
    $mon ++;
    $year +=1900;
    $today = "$year-$mon-$mday";
    I dont know about perl but Postgresql will do it for you as
    mypromt:#SELECT EXTRACT(DOY FROM DATE '2003-12-25')
    date_part
    ----------------------
    359

    1 Row(s) affected

    Shoot back if this helps !

    Regards,
    V Kashyap
  • Hodges at Dec 3, 2003 at 10:25 pm
    I have never used extract. This generates a syntax error when I added
    and select extract( DOY FROM date)

    $sth = $dbh->prepare("SELECT date,address,time,host,direction,description,
    cal,reminder from firesides and select extract( DOY FROM date)
    where date > '$dstr1' and date <= '$dstr2'");
    if ($sth->execute)
    {
    # assign each query record to an array
    $numrow = 0;
    while
    (($date,$address,$time,$host,$directions,$description,$cal,$reminder,
    $db_doy) = $sth->fetc\
    hrow)

    Tom
    On 4 Dec 2003 at 2:56, SaiHertz And Control Systems wrote:

    hodges@xprt.net wrote:
    I am getting a field of date type from PostgreSQL and an
    integer that is a number of days (1 to 3).

    select date, num_days from table
    $date, $num_days

    In my perl script I am getting the current date

    #find current date
    ($mday,$mon,$year,$doy)=(localtime(time))[3..5,7];
    $mon ++;
    $year +=1900;
    $today = "$year-$mon-$mday";
    I dont know about perl but Postgresql will do it for you as
    mypromt:#SELECT EXTRACT(DOY FROM DATE '2003-12-25')
    date_part
    ----------------------
    359

    1 Row(s) affected

    Shoot back if this helps !

    Regards,
    V Kashyap



  • Sai Hertz And Control Systems at Dec 3, 2003 at 11:23 pm
    I have never used extract. This generates a syntax error when I added
    and select extract( DOY FROM date)

    $sth = $dbh->prepare("SELECT date,address,time,host,direction,description,
    cal,reminder from firesides and select extract( DOY FROM date)
    where date > '$dstr1' and date <= '$dstr2'");
    if ($sth->execute)
    {
    # assign each query record to an array
    $numrow = 0;
    while
    (($date,$address,$time,$host,$directions,$description,$cal,$reminder,
    $db_doy) = $sth->fetc\
    hrow)

    As per me the error is quite evident because it must be some thing
    like this

    select extract(DOY FROM date'COLUMN_FIELD')

    CREATE TABLE test_date (
    "id" BIGSERIAL,
    "exp_date" DATE,
    "comp_int" int,
    PRIMARY KEY("id")
    )

    add some data
    and do as

    prompt=# select comp_int as Compared_Integer , extract( DOY from
    exp_date) as Compared_Date from test_date where id = 1 ;
    compared_integer | compared_date
    ------------------+---------------
    1 | 335
    (1 row)

    this may gve you results .

    Regards,
    V kashyap
  • Josh Berkus at Dec 4, 2003 at 4:09 pm
    Hodges,
    I have never used extract. This generates a syntax error when I added
    and select extract( DOY FROM date)
    Posting the syntax error would be useful if you want help.
    $sth = $dbh->prepare("SELECT
    date,address,time,host,direction,description, cal,reminder from firesides
    and select extract( DOY FROM date) where date > '$dstr1' and date <=
    '$dstr2'");
    One problem is that you've used a reserved word as a column name: "date".
    PostgreSQL is probably getting confused between "date" the column and "date"
    the data type. Try double quoting date: "date" (or escaped for perl:
    \"date\")

    --
    Josh Berkus
    Aglio Database Solutions
    San Francisco
  • Greg Sabino Mullane at Dec 14, 2003 at 8:16 pm
    Is there a better way to do this?
    In general, it is better to do all of your date and time calculation
    in one area. Since you are storing dates in the database, that is
    where you should attempt to do everything. Not only is it cleaner and
    easier (once you learn some of the time/date manipulation syntax), but
    there is no guarantee that perl's notion of "now" is the same as the
    database's concept of "now" (i.e. the perl script and the database may
    be on different systems).

    In specific, you can convert the date to an integer and use that and
    your numdays column to make the comparison. Using the DOY may look good:

    SELECT * FROM time4timer WHERE
    EXTRACT(DOY FROM mydate) = EXTRACT(DOY FROM CURRENT_DATE) + numdays;

    ...but it fails at the edges of the year. (December 31 DOY + 1 is not 1).

    What you really want is simply:

    SELECT * FROM time4timer WHERE mydate = CURRENT_DATE + numdays;

    I should point out that this works because of a few glossed over points:
    adding an integer to CURRENT_DATE always implies a number of days by
    default (as opposed to another unit of time), and 'mydate' must be of
    type 'date' (a timestamp would need to be explicitly cast as a date for
    the match to work).

    - --
    Greg Sabino Mullane greg@turnstep.com
    PGP Key: 0x14964AC8 200312141512

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedDec 3, '03 at 7:04p
activeDec 14, '03 at 8:16p
posts7
users5
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase