1084

Date and time storage, possible bug?

Comments for “Date and time storage, possible bug?”
 

Posted by Dom on Friday 9th February 2024 at 15:13 GMT

Trongate stores it's date time fields in int(11) fields, ostensibly because it's using the php time() function to create them. According to the php manual the time() function is based off GMT (or UTC if you prefer given that they are one and the same).

Currently, as I live in the UK, we're on GMT so I'm a tad puzzled as to why when doing some testing with the Trongate pages module the last updated time being displayed for new pages is an hour ahead.

I have not been able to determine yet whether the datetime is wrong at the point that is is being created or the function being used to read it back in is wrong. Either way something doesn't seem right. The system clock on my machine is correct as is the timezone in which it resides.
Level One Member

Dom

User Level: Level One Member

Date Joined: 12/01/2024

Posted by DaFa on Friday 9th February 2024 at 22:11 GMT

Hi Dom,

Unix timestamps are a flawed method as they are a 32 bit signed number and restricted by a limited range but that's a topic for another time, mind the pun.

I would say the 1 hour difference has something to do with daylight savings time.

function isDaylightSavingTime() {
    $currentTime = time();
    $timezone = new DateTimeZone(date_default_timezone_get());
    $transition = $timezone->getTransitions($currentTime, $currentTime);
    
    foreach ($transition as $t) {
        if ($t['isdst'] == true) {
            return true;
        }
    }
    
    return false;
}

// Example usage:
if (isDaylightSavingTime()) {
    echo "Daylight saving time is in effect.";
} else {
    echo "Daylight saving time is not in effect.";
}

This comment was edited by DaFa on Friday 9th February 2024 at 22:18 GMT

Founding Member

DaFa

User Level: Founding Member

Date Joined: 30/11/2018

Posted by Dom on Friday 9th February 2024 at 23:00 GMT

Hi Simon

I think that that's a fair assumption, which, given that we are currently in the depths of winter (although surprisingly mild where we are) means that we are running on GMT / UTC.

I have another machine I can run this on in the morning and check that. Assuming I see I a similar result I'll try and debug it and find out where the problem lies and see what can be done to correct it.

In the majority of instances this would be a mere inconvenience. I do however write software for the commercial fishing industry and an error of that magnitude can be the difference between landing fish inside or outside quota restrictions which can carry penalties that range as far as imprisonment. Consequently I've developed something of a keen eye for this sort of irregularity.

It's getting late here so I'll look at this tomorrow when I'm a little more awake.
Level One Member

Dom

User Level: Level One Member

Date Joined: 12/01/2024

Posted by Dom on Saturday 10th February 2024 at 08:42 GMT

Well that's interesting. Working on a different machine (my surface pro) down at work. I've created a new basic trongate app and created a new trongate page. Unlike my desktop at home the manage pages view is only showing me the created on info and not the last updated info that I see on my desktop. Not sure why that is yet (and yes I have edited it two or three times!).

I have also been doing a lot of research into date time handling in php and boy is that an ugly topic.

I'm going to need to create a specific module to play with dates and some views to watch the returns but my gut instinct is that you're right Simon and daylight savings is getting in the way. Not sure how one corrects that. Well that's not strictly true. I do know how I'd correct it but I rather suspect that that would be akin to a breaking change.
Level One Member

Dom

User Level: Level One Member

Date Joined: 12/01/2024

Posted by Dom on Sunday 11th February 2024 at 11:49 GMT

The surface pro anomaly turns out to be css which actually makes sense.

As for the other it only seems to affect unix timestamp, specifically the php time() function. DateTime datatypes in MySql / MariaDb are not affected. I did a lot of reading around the topic yesterday and it seems that this issue is somewhat specific to my locale (en-GB) and harks back to the time (1969 -1972) when we experimented with remaining on BST permanently with double summertime, or in layman's terms we adopted CET.

The upshot is that in our locale you can end up with an hours discrepancy. As far as Trongate pages is concerned I can live with that but it does mean that anything that could be genuinely time sensitive should use the DateTime datatype to store data.
Level One Member

Dom

User Level: Level One Member

Date Joined: 12/01/2024

Posted by DaFa on Sunday 11th February 2024 at 13:34 GMT

Hi Dom,

It looks like you have done a lot of research on the topic.

Have you thought about reading the time from an NTP server, like 'pool.ntp.org'? There are many NTP servers to use and depends on your location as to which is best for you.

Here is a function that will open a socket, gets the time from the internet and returns it as a UNIX timestamp so you can compare it with time(). Note: you will need to enable the sockets module in your PHP ini file >> `;extension=sockets` (remove the semi-colon, save and restart Apache)
function get_ntp_time($ntp_server = 'pool.ntp.org') {
	$socket = socket_create(AF_INET, SOCK_DGRAM, SOL_UDP);
	if ($socket === false) {
		return false;
	} else {
		if (socket_connect($socket, $ntp_server, 123)) {
			$request = "\x1b" . str_repeat("\0", 47);
			socket_send($socket, $request, strlen($request), 0);
			socket_recv($socket, $response, 48, 0);
			// Extract timestamp from response
			$data = unpack('N12', $response);
			$timestamp = sprintf('%u', $data[9]) - 2208988800;
			socket_close($socket);
			return $timestamp;
		} else {
			return false;
		}
	}
}
Founding Member

DaFa

User Level: Founding Member

Date Joined: 30/11/2018

×