Why does this have to be so hard?

general-dev

#1

During my thirty years as a mainframe programmer, twenty years included writing SQL queries under DB2. DB2 signs a contract whereby the "current_timestamp" will never return two timestamps with the same date/time.

This is the very reason why a timestamp is the perfect primary index for a log table, not only does it provide the when, and ensure you can reference the log entries in sequence, but it is UNIQUE.
I based an ETL on incoming entries being processed in arrival timestamp sequence because of this handshake. For this to happen, we need 6-digit millisecond precision in the timestamp.

The PHP date("Y-m-d H:i:s") function does not seem to provide more than 2-digit "seconds" precision.
WHY!? Someone like me would say... why not just attach attach another some identifier to the end of the mask, and get back the milliseconds.

This is Unix timestamp; "2018-05-25 12:35:29:123456", in fact the space between the date and time looks odd to me as well; as does the colon between the seconds and milliseconds. This is what is ideal in my mind... "2015-05-25-12.35.29.123456" a 26-byte value that represents the current date/time, and will never be a duplicate of another timestamp; no spaces, no colons, no inconsistent special characters.

Of course that is what you will get when standards are not cobbled together by an incoherent effort.

I have seen this question asked in a number of places. How do I get a current timestamp from php "WITH" milliseconds, and the response from the internet geniuses is one to a completely different question... How do I get a current timestamp "IN" milliseconds. NOT THE SAME QUESTION!!! is it?

So does anyone have an answer that doesn't require some ridiculous concoction of code in a cryptic function that no one will ever make sense of? Or, in your unbounded need to post a response will you post a response even if you didn't read the question in question?

I'm sorry, I spend way too much time seeking answers and never finding one. Forgive my rant.


#2

(new DateTimeImmutable())->format('Y-m-d-H.i.s.u');

(PHP 7.1 and up, below that the microseconds will always be 000000)

It's all in the manual: https://secure.php.net/manual/en/function.date.php


#3

The $format parameter in the function does not have to be that (Y-m-d H:i:s) format.
You may put in whatever format you want.


#4

Where can i read about the contract? All references i found about "db2 current timestamp unique" state the opposite. Besides that, DB2 - like most databases - has it's own methods to provide real unique values. Looks more like you did it wrong the last 20 years.


#5

On a low traffic website maybe, even then, how do you know that two users won't create a post/order at the same time?


#6

Why does this have to be so hard?

Appropriately enough, in the United States, it is National Wine Day!


#7

This is Unix timestamp; "2018-05-25 12:35:29:123456"

That's not a Unix timestamp.

It's not clear to me why you're trying to do this in PHP rather than in the DB. If you're trying to use the timestamp as a primary key, which is probably a bad idea if it's not a composite with something else because there no garuntee of unquiness and many applications will truncate them or mark the last 3 as 0's, then why would you just not use DEFAULT to set the current timestamp? This is possible in all DBs that I've worked with, including DB2. But honestly, even a sequence is much better than a timestamp. Doing this outside the DB + using a data type not meant to be used for a primary key, is a recipe for corrupted data.

If you're going to go through the effort of assigning Primary Keys in PHP, then I highly suggest using something like a UUID, which is designed to be a primary key and is garunteed uniqueness even across tables.


#8

Wait, what? Your post starts out strong with UUID and everything, and then you link to the uniqid function? What's that got to do with anything?


#10

Good call. I don't write PHP often and that was the first result for googling "PHP UUID", I didn't really read it. I removed it. There looks like there a few ways to do it in PHP and not a native way, but they all look fairly straightforward.


#11

No worries :slight_smile:

The most common one people use, and pretty much the unofficial defacto standard is ramsey/uuid


#12

Yeah, I wanted to mention that but I feel like OP is not going to include 3rd party libraries.


#13

I think it was true years ago. But not for partitioned databases. That's why IBM added the generate_unique function and, later, sequences. Now you can also generate timestamps including current_timestamp with anything from second to nanosecond granularity. There's no way this would work well for seconds! Even at the original, fixed microsecond granularity this could now be considered a bottleneck.


closed #14

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.