Timestamp adding 10 years

[TechnoDiver]

For posts, comments etc in my DB the date_added column is set to bigint() with the current timestamp as default.

When retrieving the timestamp from the DB I'm using this formatting ->

"date" => date("F d, 20y H:i:s", $obj->date_added)

An item that has this timestamp in the DB -> 20211021141214

Is displaying this date/time -> October 06, 2031 14:13:34

I've been doing it this way for awhile and I just noticed that the date is 10 years off even though the time stamp says 2021 at the beginning.

What's going on here?

 

P.S. Oddly enough, while typing this a few things to check came to mind. I put everything back to normal and now this timestamp ->20211026114045

is returning this date/time -> DECEMBER 03, 2031 03:34:05

 

I don't get it. That last one looks to me as it should be Oct. 26, 2021 11:40:45 which is exactly when I posted it on my system clock. I haven't been able to find anything to explain this and I've not manipulated anything programmatically. Only the date() format that I provided above

[requinix]

What's going on here is that you think "20211021141214" is a number. It is not. It is a string made up of the characters 0-9.

That string is not a useful timestamp. Either store your date as a regular DATETIME/TIMESTAMP like "2021-10-21 14:12:14" (easier to work with in SQL) or store it as a Unix timestamp like 1634825534 (easier to work with in PHP).

[Barand]

I think you'll find that using that number as a unix timestamp is out by a little more than 10 years. It appears to be only 10 years out becouse yo are hard-coding the first 2 digits of the year (why?)

$date = date("F d, Y", 20211021141214);
echo $date;                                // October 06, 642431

If you use DateTime class

$dt = new DateTime(20211021141214);
echo $dt->format('F d, Y H:i:s')        // October 21, 2021 14:12:14

Or, if you continue to use date(), convert the string to a valid timestamp

$date = date("F d, Y", strtotime('20211021141214'));
echo $date . '<br>';                                    // October 21, 2021

 

Edited October 26, 2021 by Barand

[TechnoDiver]

8 hours ago, requinix said:

What's going on here is that you think "20211021141214" is a number. It is not. It is a string made up of the characters 0-9.

That string is not a useful timestamp. Either store your date as a regular DATETIME/TIMESTAMP like "2021-10-21 14:12:14" (easier to work with in SQL) or store it as a Unix timestamp like 1634825534 (easier to work with in PHP).

OK, I get it. The 2 factors that I used to determine how to store and pull date/times was that I wanted the date/time to be set automatically when content was sent to the DB ( thus using current_timestamp as the default value) and I guess I thought that bigint() would be different than it turned out to be.

The other factor is that I needed it in a format that I can do math with so as to calculate time integers etc. I thought having a bigint() type with current_timestamp as default would accomplish this, it doesn't as we both now know ( you already knew, I just learned) I get so tied up in so many different aspects of this project that I just noticed this recently.

As I sit back and contemplate the things I'm reading from you and Barand I'm thinking I'll store them as a Timestamp type with current_timestamp as the default and then to do the math when needed I'll convert it to Unix timestamp after retrieval from the DB. Would that be the most efficient way to do this?

 

8 hours ago, Barand said:

think you'll find that using that number as a unix timestamp is out by a little more than 10 years. It appears to be only 10 years out becouse yo are hard-coding the first 2 digits of the year (why?)

I was hardcoding in the first 2 digits because without it it just showed '31' ( which I just noticed, until today I was seeing it as '21, which was probably just my oversight) I get pretty overwhelmed with all the new things I have to learn and research for this project and overlook some relatively obvious things often.

But I think I now know what I have to do - store them in DB as timestamp type, current_timestamp default and then change them to Unix timestamps when I want/need to do math with them for time interval etc.

If either of you gentlemen think there is a better way I'm definitely down to hear about it. Thanks both for the replies

[requinix]

9 minutes ago, TechnoDiver said:

The other factor is that I needed it in a format that I can do math with so as to calculate time integers etc.

Don't do date math on Unix timestamps.

MySQL is perfectly capable of doing date arithmetic using DATETIMEs with INTERVALs, and in PHP you should always load the date into a DateTime object and use its add/sub/modify methods.

 

9 minutes ago, TechnoDiver said:

As I sit back and contemplate the things I'm reading from you and Barand I'm thinking I'll store them as a Timestamp type with current_timestamp as the default and then to do the math when needed I'll convert it to Unix timestamp after retrieval from the DB. Would that be the most efficient way to do this?

See above.

 

9 minutes ago, TechnoDiver said:

If either of you gentlemen think there is a better way I'm definitely down to hear about it. Thanks both for the replies

Details would be nice. Especially where (SQL or PHP?) and when (manipulating data or displaying values to the user?) you want to do the math.

[gizmola]

I'm glad this was said, because I was thinking the same thing -- why use an 8 byte integer, when MySQL has smaller date/time datatypes that also support SQL functions natively, with no conversion or clientside logic required?

I have written a few blog posts over the years about these topics, that might be of interest:

CURDATE and NOW

Find Next Monday

About MySQL Timestamps

[TechnoDiver]

5 minutes ago, requinix said:

MySQL is perfectly capable of doing date arithmetic using DATETIMEs with INTERVALs, and in PHP you should always load the date into a DateTime object and use its add/sub/modify methods.

Ok, thank you, I didn't know this about either. I will be researching it when the time comes. I wish I could save responses to make them accessible from my profile.

I can't remember every single thing I learn on here and there's been at least twice I've been called out on it where responders have replied the same things to me more than once and let me know they had already answered that issue lol anyways, c'est la vie

I appreciate the help. I'm not to a point that I need intervals yet so I'm doing it like this, and it's working fine now ->

in DB -> type: TIMESTAMP, default: CURRENT_TIMESTAMP

PHP ->

"date" => date("F d, 20y H:i:s", strtotime($obj->date_added))

Thanks again

[TechnoDiver]

1 minute ago, gizmola said:

I have written a few blog posts over the years about these topics, that might be of interest:

excellent. I think I'll never have problems with timestamps again after this

[gizmola]

One thing to be aware of, is that mysql has made some internal changes in regards to storing of fractional seconds that doesn't make timestamp the big win it once was.   A mysql datetime only requires 5 bytes + whatever fractional second storage you need.  If you don't use/need fractional seconds, you can have a 5 byte Datetime column, which doesn't come with any of the timestamp properties, quirks or baggage.  Use whichever type makes more sense to you.

[dodgeitorelse3]

you still have 20 hardcoded with y to show it as 2021. Instead of y use Y.

[Barand]

2 minutes ago, dodgeitorelse3 said:

you still have 20 hardcoded with y to show it as 2021. Instead of y use Y.

+1

It was coding like that that almost destroyed civilization as we know it back in the Y2K days.