Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » Timestamp accuracy in postgres and upp
Timestamp accuracy in postgres and upp [message #33055] Tue, 05 July 2011 15:44 Go to next message
Zbych is currently offline  Zbych
Messages: 327
Registered: July 2009
Senior Member
Hi,

Definition of TIME in database schematic doesn't specify accuracy and (at least) postgres keeps time in micro seconds. Since upp time accuracy is 1s, it sometimes leads to some problems. Maybe there should be another definition of TIME that has the same accuracy like upp? What do you think?

My proposition is to add following definitions to PostgreSQLSchema.h:

#define TIME_SEC(x) COLUMN("timestamp(0)", Time, x, 0, 0)
#define TIME_SEC_(x) COLUMN_("timestamp(0)", Time, x, 0, 0)

Re: Timestamp accuracy in postgres and upp [message #33090 is a reply to message #33055] Wed, 06 July 2011 14:30 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 14105
Registered: November 2005
Ultimate Member
Zbych wrote on Tue, 05 July 2011 09:44

Hi,

Definition of TIME in database schematic doesn't specify accuracy and (at least) postgres keeps time in micro seconds. Since upp time accuracy is 1s, it sometimes leads to some problems. Maybe there should be another definition of TIME that has the same accuracy like upp? What do you think?

My proposition is to add following definitions to PostgreSQLSchema.h:

#define TIME_SEC(x) COLUMN("timestamp(0)", Time, x, 0, 0)
#define TIME_SEC_(x) COLUMN_("timestamp(0)", Time, x, 0, 0)




Actually, perhaps TIME should be defined in seconds accuracy...

However, what kind of issues does it leads to? I would say you store seconds, you are about retrieve seconds from DB, right?

Mirek
Re: Timestamp accuracy in postgres and upp [message #33092 is a reply to message #33090] Wed, 06 July 2011 15:02 Go to previous messageGo to next message
Zbych is currently offline  Zbych
Messages: 327
Registered: July 2009
Senior Member
I don't want to change TIME definition because of backward compatibility, but it is your decision.

In my application I keep some logs in a database. Once in a while another application transfers those logs from the database to main server and I use timestamp to remember which logs were already transfered.
If the last log has timestamp 17:00:00.5 (timestamp is added automatically by postgres), upp rounds it to 17:00:00. Next time I will search for logs with timestamp >17:00:00 and the log with timestamp 17:00:00.5 will be transfered again. That is why I need the same timestamp resolution in both upp and database.

[Updated on: Wed, 06 July 2011 15:04]

Report message to a moderator

Re: Timestamp accuracy in postgres and upp [message #33113 is a reply to message #33092] Thu, 07 July 2011 13:17 Go to previous messageGo to next message
zsolt is currently offline  zsolt
Messages: 698
Registered: December 2005
Location: Budapest, Hungary
Contributor
I think, it would be easier to use a 'serial' field.
Re: Timestamp accuracy in postgres and upp [message #33299 is a reply to message #33092] Sat, 23 July 2011 09:15 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 14105
Registered: November 2005
Ultimate Member
Zbych wrote on Wed, 06 July 2011 09:02

I don't want to change TIME definition because of backward compatibility, but it is your decision.

In my application I keep some logs in a database. Once in a while another application transfers those logs from the database to main server and I use timestamp to remember which logs were already transfered.
If the last log has timestamp 17:00:00.5 (timestamp is added automatically by postgres), upp rounds it to 17:00:00. Next time I will search for logs with timestamp >17:00:00 and the log with timestamp 17:00:00.5 will be transfered again. That is why I need the same timestamp resolution in both upp and database.



What I am not getting is why it finds the log the first time, actually...

I mean, if the select is something like

Select().From(LOG).Where(timestamp >= 16:00 && timestamp < 17:00)

(and than move forward).

In fact, I am posting this because I have very similar arrangemnt in one of mine apps, so I would like to know if I am missing something Smile
Re: Timestamp accuracy in postgres and upp [message #33302 is a reply to message #33299] Sat, 23 July 2011 18:14 Go to previous messageGo to next message
Zbych is currently offline  Zbych
Messages: 327
Registered: July 2009
Senior Member
This log can be found for the first time because main server doesn't have it in it's database and uses timestamp of earlier log as a start point.

[Updated on: Sat, 23 July 2011 20:07]

Report message to a moderator

Re: Timestamp accuracy in postgres and upp [message #33381 is a reply to message #33302] Sat, 30 July 2011 18:59 Go to previous message
mirek is currently offline  mirek
Messages: 14105
Registered: November 2005
Ultimate Member
Zbych wrote on Sat, 23 July 2011 12:14

This log can be found for the first time because main server doesn't have it in it's database and uses timestamp of earlier log as a start point.




I see.

OK, patch applied, TIME_SEC added...

Mirek
Previous Topic: SqlArray and data validation
Next Topic: [SQLITE] New version (3.7.0) introduces write-ahead logging.
Goto Forum:
  


Current Time: Fri Nov 01 00:25:28 CET 2024

Total time taken to generate the page: 0.00493 seconds