Postgres equivalent to Sql Servers @@DBTS

Question

I am mainly from a Sql Server background, and following some issues with getting MySql to work with the Microsoft Sync Framework (namely it does not cater for snapshots), I am having to look into Postgres and try to get that working with the Sync Framework.

The triggers that are needed include a call to function "@@DBTS", but I am having trouble finding an equivalent in Postgres for this.

From the microsoft documentation for this it says:

@@DBTS returns the current database's last-used timestamp value. 
A new timestamp value is generated when a row with a timestamp 
column is inserted or updated.

In MySql it was the following:

USE INFORMATION_SCHEMA;
SELECT MAX(UPDATE_TIME) FROM TABLES WHERE UPDATE_TIME < NOW();

Can anyone tell me what this would be in Postgres?

IIRC, Timestamps have a different meaning at Microsoft. In Postgres, they are just ordinary "date+time" datatypes. The sequence-types you probably want (related to rowversioning) do exist, but I don't think they are particular useful at application level. What is it exactly that you exactly need them for? Do you want sub-transaction time granularity? — wildplasser
– wildplasser, Commented Mar 20, 2012 at 11:04
This is all for the sync framework, I have used the sql server provisioning classes to correctly provision all tables/triggers/stored procedures etc, and I am now trying to mimik this in the Postgres database (as there is no provisioning class). It does an insert to a tracking table, putting "@@DBTS + 1" into column "local_create_peer_timestamp", and puts "GETDATE()" into column "last_change_datetime", so it seems to differentiate between them, and I did not want to break whatever reason they did this. Also, any code in the sync framework will expect (I assume) the data to be in this format. — eyeballpaul
– eyeballpaul, Commented Mar 20, 2012 at 11:12
PostgreSQL does not keep track when a table was last modified. So there is no equivalent for SQL Server's DBTS nor for MySQL's UPDATE_TIME — user330315
– user330315, Commented Mar 20, 2012 at 11:22
Well it stores it as a bigint, so I am not 100% sure how it works behind the scenes, but it must do something like a select later for all rows that have this bigint being greater than a value, and that value is the int representation of the timestamp. Is there maybe a way in postgres to get the current date/time as a number? i.e. this could maybe be the total number of seconds? And I could see if this works for the sync framework? — eyeballpaul
– eyeballpaul, Commented Mar 20, 2012 at 11:30
It seems the OP wants to impose order inside a transaction. Except for the order of invocation for triggers (which is alphabetical in Postgres, BTW), I cannot see any sane reason to want that. — wildplasser
– wildplasser, Commented Mar 20, 2012 at 11:57

user330315 · Accepted Answer · 2012-03-20 15:48:55Z

1

PostgreSQL does not keep track when a table was last modified. So there is no equivalent for SQL Server's @@DBTS nor for MySQL's INFORMATION_SCHEMA.TABLES.UPDATE_TIME.

You also might be interested in this discussion:

http://archives.postgresql.org/pgsql-general/2009-02/msg01171.php

which essentially says: "if you need to know when a table was last modified, you have to add a timestamp column to each table that records that last time the row was updated".

answered Mar 20, 2012 at 15:48

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres equivalent to Sql Servers @@DBTS

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related