Question about current_timestamp

[ztminhas]

Good Morning,

I am rather confused about the default value CURRENT_TIMESTAMP. Here is what I have so far:

RECREATE TABLE TABLE1
(
	F1 INTEGER NOT NULL,
	CUR_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE UNIQUE ASC INDEX IDX_TABLE1 ON TABLE1(F1);
ALTER TABLE TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY(F1);

Then from Basic I call the follow code:

EXECUTE BLOCK AS
DECLARE I INT = 0; 
BEGIN
     DELETE FROM TABLE1 WHERE F1 IS NOT NULL;
     EXECUTE STATEMENT 'ALTER TABLE TABLE1 DROP CONSTRAINT PK_TABLE1';
     EXECUTE STATEMENT 'DROP INDEX IDX_TABLE1';
     WHILE (I < 1000000) DO
     BEGIN 
          INSERT INTO TABLE1(F1) VALUES (:I);
          I = I + 1; 
     END
     EXECUTE STATEMENT 'CREATE UNIQUE ASC INDEX IDX_TABLE1 ON TABLE1(F1)';
     EXECUTE STATEMENT 'ALTER TABLE TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY(F1)';
END

The time takes a little bit under a minute to execute.The SQL I use to get my metric is:

SELECT MIN( "F1" ), MAX( "F1" ), MIN( "CUR_TIME" ), MAX( "CUR_TIME" ) FROM "TABLE1"
having MIN( "CUR_TIME" )<MAX( "CUR_TIME" )

And the result is empty, which implies that every value in the CUR_TIME column is the same. This doesn’t make sense since the total time for the insert takes under a minute. So what does CURRENT_TIMESTAMP indicate? Is there a way to get the proper time stamp?

[RobertG]

Seems it is a problem with EXECUTE BLOCK. All inserts in this block will get the same timestamp.

Test it without such a construction you could see: CURRENT_TIMESTAMP changes it’s value every time you refresh a query or view. Have created a view to get the the thousands seconds and could see this behavior.

Have tested this without Basic, because it is SQL code. Created 1 000 000 rows and all timestamps will show the same value.

[mariosv]

Maybe a COMMIT; after insert every value. I didn’t test.

[ztminhas]

Hello,

I found the solution:
https://firebirdsql.org/refdocs/langrefupd20-now.html

'NOW' always returns the actual date/time, even in PSQL modules, where CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP return the same value throughout the duration of the outermost routine. This makes 'NOW' useful for measuring time intervals in triggers, procedures and executable blocks.

I modified the table definition:

RECREATE TABLE TABLE1
(
	F1 INTEGER NOT NULL,
	CUR_TIME TIMESTAMP DEFAULT 'NOW'
);

This works.