Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004

    Unanswered: how to insert row at a particular date?

    is there a way to insert a row in a table displaying only the timestamp on a particular day? Could you insert a row returning the timestamp every sunday? Thanks!

  2. #2
    Join Date
    Mar 2004
    I wouldnt know how to do it in postgresql.
    I dont think its possible. You probably have to do it using a PERL script or sumthing.

  3. #3
    Join Date
    Jun 2004
    I don't understand. How do you "INSERT INTO" a table and then display anything?

    The INSERT command displays nothing, certainly nothing to do with timestamps.

    Please explain.

  4. #4
    Join Date
    Mar 2004
    oops sorry, I meant contain, not display . I have a table with a whole bunch of columns, one of which contains the timestamp when a row is added to the table. I'm making a select query which displays the entries in my table by week of month, each week beginning on a monday and ending on a sunday, with the 1st monday of the month marking the start of week 1 for the month. I used this:

    SELECT * FROM table1 t WHERE EXTRACT (WEEK FROM (t.datetimestamp, 'MM DD YYYY')) =
    (SELECT distinct EXTRACT(WEEK FROM (t.datetimestamp, 'MM DD YYYY'))
    FROM table1 t WHERE
    (EXTRACT (MONTH FROM (t.datetimestamp, 'MM DD YYYY'))= [month])
    AND (to_char ((t.datetimestamp, 'MM DD YYYY'), 'W')= [week of month])
    AND (EXTRACT(DOW FROM (t.datetimestamp, 'MM DD YYYY')) = [day of week])
    AND (EXTRACT (YEAR FROM (t.datetimestamp, 'MM DD YYYY'))= [year]));

    although it doesn't work for weeks when there aren't any entries for mondays. I need something to make sure that there would always be an entry for all mondays. Or if you could suggest a new query or a way to change the week format in postgres, i'd really appreciate it.

  5. #5
    Join Date
    Jun 2004
    If I understand you, this "should" be rather doable.

    What you want is a list of entries that fall on a particular week of month (WOM) and your definition varies from Pg's.

    Off the top of my head (and I may have some errors here), your WOM (1 thru 5) can be calculated by finding the WOM of the Monday prior to the date in question. This can be calculated by taking an offset of the current date.

    The EXTRACT(DOW) function returns 0 thru 6, so the date of Monday prior to any date can be found by finding the difference between a date and the prior monday.

    If my math is right, the offset is: - (EXTRACT(DOW, datetimestamp)+6)%7
    So the previous mondat is: datetimestamp-(EXTRACT(DOW, datetimestamp)+6)%7

    Now just find the WOM of the previous monday:

    TO_CHAR("w", datetimestamp-(EXTRACT(DOW, datetimestamp)+6)%7)

    Next, do a join or whatever select you like:

    SELECT *
    FROM table1 t
    WHERE TO_CHAR("w", datetimestamp-(EXTRACT(DOW, datetimestamp)+6)%7) = '1'

    Or ='2', or whatever.

    If you want more speed with lots of data, index the expression in the WHERE clause.

    Hope this helps,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts