Postgres Triggers & Unix Time - Anyone?

[tim_perdue]

Anyone know how to get unix time (seconds since 1970) inserted into a field in the database?

Also, I want to create a trigger that, whenever the users table is updated, it will update the time field with the unixtime.

Any help is appreciated - I haven't messed with triggers at all yet.

[Anon]

Tim,
This is all SQL Server specific, but it may help. I was here trying to learn a little about PHP and saw your question.

This function will convert the current date/time into unix time, although it is MS SQL Server specific, you should be able to convert.

select (datediff(ss, '1/1/1970', getdate())+ 18000) as UnixTime

The 18000 is the GMT offset for EST and getdate() returns the current date/time.

This trigger would work in SQL Server:
UPDATE tablename
SET UnixTime = (datediff(ss, '1/1/1970', getdate())+ 18000)
FROM INSERTED i
JOIN tablename t ON i.key = t.key

Tim Perdue, PHPBuilder.com wrote:

Anyone know how to get unix time (seconds since 1970) inserted into a field in the database?

Also, I want to create a trigger that, whenever the users table is updated, it will update the time field with the unixtime.

Any help is appreciated - I haven't messed with triggers at all yet.

[Anon]

I forgot the CREATE ON statement, this would work.

CREATE TRIGGER triggername
ON tablename
FOR UPDATE
AS
UPDATE tablename
SET UnixTime = (datediff(ss, '1/1/1970', getdate())+ 18000)
FROM INSERTED i
JOIN tablename t ON i.key = t.key

[smarlowe]

and if you just need a time stamp for when a row was created, you can do it with default like so:

create table (date timestamp default NOW());

Of course, you may want more than just a simple timestamp in a table... :⁾