Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an application where in the user can see the history of all questions he has posted. These are have to be displayed in the order newest to oldest. I am implementing this in Android.
I have two questions
1) I have not stored the time at which the question was asked. Does postgres, internally store the timestamp of when the row was inserted?
2) In a mobile device, only a few posts can be displayed at a time. So when the user scrolls, the new rows should be displayed. So in the database, is it possible to query like give me the top 10 rows, 10-20 rows and so on.

share|improve this question
    
Is the expiry time calculated based on a fixed amount of time from when the row was inserted? If so, you can work out the insertion time. In fact, if you only need the order you don't even need to do a calculation. –  Phil Aug 23 '12 at 10:57
    
@Phil : no it is chosen by the user. Forget the expiry time. My question was does postgres internally store the timestamp of when the row was inserted? –  Ashwin Aug 23 '12 at 11:26
    
Yes, I know what your question was. I was merely asking to see if there was another way of achieving the desired result because Postgres doesn't store the insert time for each row... No need to be rude. –  Phil Aug 23 '12 at 11:29
    
@Phil : I was not being rude (sorry if you felt that way). I wanted to imply that I should not have said anything about the expiry time. I have removed that from the question as well.....That means I will have to change the table schema. So can you answer the second question assuming that I have column called timestamp. –  Ashwin Aug 23 '12 at 11:44
add comment

1 Answer

up vote 4 down vote accepted

1) I have not stored the time at which the question was asked. Does postgres, internally store the timestamp of when the row was inserted?

No. You can create one by using TIMESTAMP WITH TIME ZONE (timestamptz for short) and setting a default using an appropriate function. (You can choose the timestamp of the start of database transaction, the start of execution of the INSERT statement, or the moment the row is processed -- whichever best suits your needs.) Don't be tempted to use TIMESTAMP WITHOUT TIME ZONE (the default if you just say TIMESTAMP) -- you would regret that the first time you hit a daylight saving time boundary.

2) In a mobile device, only a few posts can be displayed at a time. So when the user scrolls, the new rows should be displayed. So in the database, is it possible to query like give me the top 10 rows, 10-20 rows and so on.

This one is a bit tricky.

  • You can use non-transactional cursors, but that prevents database maintenance operations for as long as the cursor is open, so you want to make sure that the time is not unbounded.

  • You can use OFFSET and LIMIT, but that gets slow if you need a big OFFSET, since it has to read through all the preceding rows each time. It also gets weird if the data is being modified while you are paging around.

  • You can save values for the ends of what is being displayed and run a query with LIMIT to get the rows just outside that. This one performs well; the only real down side is programming complexity.

  • You can run the whole list to storage somewhere outside the database when they first request it, and do your scrolling from that. It may even be possible to use a temporary table for this, depending on other details.

This is a problem that people are always sorting out, and I don't think that any one answer is the best for all situations.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.