By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,255 Members | 2,640 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,255 IT Pros & Developers. It's quick & easy.

getting the last record using timestamp

kirara
P: 19
hi all,
I have a table common_memory_4_memory it has five columns which are: freememory
usedmemory, totalmemory, sourcekey(machine IP(name)) and the timestamp
i want to get the newest record for a specific machine using the timestamp column

sample:
174156;"machine2";1166200260
173776;"machine2";1166200320
161704;"machine3";1166200380
163704;"machine1";1166200440
160296;"machine2";1166200500
160296;"machine3";1166200500

what should I do?
thanks in advance
Dec 16 '06 #1
Share this Question
Share on Google+
9 Replies


Expert 100+
P: 700
hi all,
I have a table common_memory_4_memory it has five columns which are: freememory
usedmemory, totalmemory, sourcekey(machine IP(name)) and the timestamp
i want to get the newest record for a specific machine using the timestamp column

sample:
174156;"machine2";1166200260
173776;"machine2";1166200320
161704;"machine3";1166200380
163704;"machine1";1166200440
160296;"machine2";1166200500
160296;"machine3";1166200500

what should I do?
thanks in advance
I'm nor sure if i understand u'r proble (u wrote tabla has 5 columns, but sample tables has only 3 separated by ';' )

select * from common_memory_4_memory where (machine='xxxx') timestamp=(select max(timestamp) from common_memory_4_memory);
Dec 17 '06 #2

Expert 100+
P: 534
With this table schema you will have troubles getting the latest record.
It looks that your timestamp is a UNIX time (time in seconds since 1970-01-01). It has resolution of 1 second, which does not provide enough granularity for creating unique records.
In fact with your data, a query relying on max timestamp would return two last records, because they both have the same timestamp: 1166200500

You may consider adding another field, something like
rec_id serial primary key

This would allow to select by max(rec_id) and will give you an indexed field to speed up the query.

Alternative query, which sometimes may give a better performance would be this:

Expand|Select|Wrap|Line Numbers
  1. select * from myTable order by rec_id desc limit 1;
Dec 17 '06 #3

kirara
P: 19
I'm nor sure if i understand u'r proble (u wrote tabla has 5 columns, but sample tables has only 3 separated by ';' )

select * from common_memory_4_memory where (machine='xxxx') timestamp=(select max(timestamp) from common_memory_4_memory);
thanks it worked really fine

With this table schema you will have troubles getting the latest record.
It looks that your timestamp is a UNIX time (time in seconds since 1970-01-01)....
You may consider adding another field, something like
rec_id serial primary key
it is a UNIX time, but can't I use the MAX function with UNIX time, and I can't add anything to this table because it handled by another program
Dec 17 '06 #4

Expert 100+
P: 534
Of course you can use the max function with these integer values, the issue here is that this query may return more than one record... which may or may not be good for your program.
Dec 17 '06 #5

kirara
P: 19
Of course you can use the max function with these integer values, the issue here is that this query may return more than one record... which may or may not be good for your program.
I see what do you mean, becuase it returned more than one query, but I found this querry in this site http://developer.postgresql.org/pgdocs/postgres/sql-altertable.html:
"To change an integer column containing UNIX timestamps to timestamp with time zone via a USING clause":
Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE foo
  2.     ALTER COLUMN foo_timestamp TYPE timestamp with time zone
  3.     USING
  4.         timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
is this better to be used instead of UNIX timestamp?
Dec 19 '06 #6

Expert 100+
P: 534
I think your ultimate goal it to have something that can be used to identify the last record inserted into table table.
One very simple way of doing that is to use a unique id whcih gets its value from the built-in sequence generator, its type is serial and I mentioned it before. From what you said earlier it looks that you can alter a field, but not the table, so I guess this option is out.

Another dirty trick is to use a built-in record ID (oid) which may or may not exist depending on how your table was created.
You can check on oids with this query:
Expand|Select|Wrap|Line Numbers
  1. select max(oid) from myTable;
  2.  
Usually it works, but if your database is very, very large at some point the oids are starting to wrap around... then this is not a good solution...

Next the timestamps. You can modify the field to provide a much better resolution. Not ideal for a purist, but for all practical purposes this may be good enough.

I am not sure whether you'll achieve this with the sql you posted, because it seems that resulting datatype will be timestamp with time zone
Unless the default resolution has changed in latest versions you'll be back to 1 second... meaning new format, the old problem.
Here's a quick test I just ran:

Expand|Select|Wrap|Line Numbers
  1. create table foo (
  2.     id serial primary key,
  3.     t1 timestamp(5) not null,
  4.     t2 timestamp(6) not null
  5. );
  6.  
  7. insert into foo (t1, t2) values (now(), now());
  8.  
  9. select  *  from foo;
  10.  id |            t1            |             t2             
  11. ----+--------------------------+----------------------------
  12.   1 | 2006-12-19 13:59:55.1083 | 2006-12-19 13:59:55.108299
  13. (1 row)
  14.  
You see, timestamp(5) gives me resolution down to 10K-th of a second,
timestamp(6) takes it to 1 millionth of a second!

I suppose this is something you can use.
Hope it helps, let me know if you run into any problems.

michael
Dec 19 '06 #7

Expert 100+
P: 534
Few more details that may help you.
First, you probably want to test your code before you make changes to the real table.

Expand|Select|Wrap|Line Numbers
  1. create table myTestTab as (select * from myRealTab);
  2.  
This will create a copy of the real table.
Now you can modify the code you quoted earlier to create a datatype with a fine enough resolution. Just replace

TYPE timestamp with time zone
with
TYPE timestamp(x)

where x could be 5 or 6

Next, select few rows from the new table and see if they look correct.
Run your query with the "max(timestamp)" clause against this table.

If everything looks fine you can run this conversion on the real table.
At the same time you need to modify code that inserts new rows into the table.
Instead of getting/inserting the UNIX time you should use the Postgres function now() (see example in my previous posting)

And finally, unless this is and always will be a small table, you may want to create an index on the timestamp column. This will help to speed up all your queries relying on timestamp.
If I remember correctly you can do something like this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. create index idx_tstamp on myTable(foo_timestamp);
  3.  
Dec 20 '06 #8

kirara
P: 19
Thanks for the reply
I will try it and back for feedback
Dec 21 '06 #9

kirara
P: 19
THANKS ALOT you are Awesome
You can check on oids with this query:
Expand|Select|Wrap|Line Numbers
  1. select max(oid) from myTable;
  2.  
my tables don't have an OIDs, but I tried the following and its great because it gives an accurate records without any duplication:


...to create a datatype with a fine enough resolution. Just replace

TYPE timestamp with time zone
with
TYPE timestamp(x)

where x could be 5 or 6
thanks again i'll try it with the whole database
Dec 21 '06 #10

Post your reply

Sign in to post your reply or Sign up for a free account.