0032456: Date columns are created as int4 type in PostgreSQL despite PHP is 64 Bit (expected int8 types according to PHP_INT_SIZE) - MantisBT - Signup temporarily disabled due to spam

ID	Project	Category	View Status	Date Submitted	Last Update

0032456	mantisbt	db schema	public	2023-04-26 08:33	2023-04-26 11:53

Reporter	mpeterma	Assigned To
Priority	normal	Severity	tweak	Reproducibility	always
Status	acknowledged	Resolution	open
Platform	PC Server	OS	NetBSD	OS Version	9.3
Product Version	2.25.7

Summary	0032456: Date columns are created as int4 type in PostgreSQL despite PHP is 64 Bit (expected int8 types according to PHP_INT_SIZE)
Description	While writing some queries to evaluate time tracking I just noticed in my PostgreSQL database that all columns that have timestamps were created with int4 data type only. My PHP runs on a native 64 bit system and the internal constant PHP_INT_SIZE returns 8, i.e. from PHP's point of view an integer is apparently 8 bytes in size. I know that the issue is still far away and nobody knows what will be then. I found another ticket 0007844 about this, which was closed with, among other things, the comment that a 64-bit version of PHP should not have this problem. So now I am unsure if this is a bug, or a PostgreSQL specific issue, or a configuration error on my part. I also unfortunately still have too little knowledge of how to independently figure out in Adodb how the column types are derived from the variable types, and at what point this happens.
Steps To Reproduce	Configure MantisBT on PHP 8.1.14 on a 64 Bit System (NetBSD in my case) and use PostgreSQL as the database Inspect database schema after creation, column types e.g. date_submitted in mantis_bugnote_table
Tags	No tags attached.

dregad 2023-04-26 11:53 developer ~0067708	The size of PHP integers depends on the system's byte size, but this is not the case for the SQL integer type, which is fixed to 4 bytes. There is no direct or automatic correlation between the two, and moreover this not specific to PostgreSQL. The Mantis schema currently creates date/timestamp columns with ADOdb `I` meta type, which translates to INTEGER. I believe you misunderstood the explanation in 0007844:0041554. What it is saying, is that we would need to end support for 32-bit PHP, and update our data model to use to 8-byte integers (SQL bigint / ADOdb `I8` meta type) to store date/timestamp columns, which is not something that really needed to happen back in 2014 when the note was written. I guess the situation is somewhat different nowadays, with 64-bit PHP being pretty much ubiquitous and the 2038 problem getting closer. Anyway I don't think it would make sense to implement a dynamic schema generation based on PHP_INT_SIZE for date/timestamp columns, so at some point we'll need to bite the bullet and make the switch to `i8` in our schema, as suggested in 0007844.