Should I Modify The Timestamp Precision in the Database? | Laravel | Page 1

forest meadow Sep 4, 2025, 6:23 PM

#

So we're having an issue with a music player. We have 3 models involved into this:
User, Song, Play

Play:

user_id,
song_id,
played_at,

So when a user is in the music player and hits the "next" btn, a new Play model is created with a new song for them to listen. To know the order of the queue we are using the played_at field. And the problem comes now. Sometimes, users are quick enough to create Play records that have the same played_at, in the same exact second. Imagine pressing the next btn quickly until the user gets the song they want. So yeah, then, if presses the "back" button (to go to previous record being played), it bugs out and doesnt show the correct song that was played before cuz of the exact same played_at field's value!

How would u solve this issue? I thought about adding microseconds on played_at field, so its impossible that the user creates more than one play having the same exact value.

I ask how would u solve it cuz im unsure if modifying the MySqlGrammar to set the date format like this is a great idea or can introduce unexpected behaviours - 'Y-m-d H:i:s.u'

#

I dont wanna rely on the id field, since the user may create a new Play manually. Like, imagine the user listened the song via another app but wants to register it in this app. Then they create a new Play record manually with the song and played_at time.

So the id won't reflect the real order.

#

And neither we are relying on updated_at/created_at fields

spark trench Sep 4, 2025, 6:33 PM

#

Shouldn't require modifying the MySqlGrammer, you can specify the date format in the casts https://laravel.com/docs/12.x/eloquent-mutators#date-casting

#

you'd also have to set the precision in the migrations

forest meadow Sep 4, 2025, 6:44 PM

#

is required i think

#

im testing it

#

in AppServiceProvider's boot method:
DB::connection()->setQueryGrammar(new \App\Database\Query\Grammars\MySqlGrammar(DB::connection()));

#

app/Database/Query/Grammars/MySqlGrammar.php

<?php

namespace App\Database\Query\Grammars;

class MySqlGrammar extends \Illuminate\Database\Query\Grammars\MySqlGrammar
{
    public function getDateFormat(): string
    {
        return 'Y-m-d H:i:s.u';
    }
}

forest meadow Sep 4, 2025, 6:46 PM

#

spark trench Shouldn't require modifying the MySqlGrammer, you can specify the date format in...

'played_at' => 'datetime:Y-m-d H:i:s.u', like this u mean?

forest meadow Sep 4, 2025, 6:48 PM

#

forest meadow is required i think

if i dont use the mysql grammar, it doesnt work

im testing it like this:

$now = now();
Play::factory()->count(1)->create([
    'played_at' => $now,
]);
Play::factory()->count(1)->create([
    'played_at' => $now,
    'song_id' => 3,
]);

if i use the modified mysql grammar, it works. otherwise, the terminal keeps thinking forever

#

just now, ive also make it so the played_at is unique. in case it isnt, to prevent errors, i do:

protected static function booted(): void
{
    static::creating(function (Play $play): void {
        $playedAt = $play->played_at->copy();

        while (Play::query()->where('played_at', $playedAt)->exists()) {
            $playedAt->addMicroseconds(1);
        }

        $play->played_at = $playedAt;
    });
}

#

so my guess is that if i dont use the modified mysql grammar, it gets stucked into the loop. im gonna check it out now

spark trench Sep 4, 2025, 6:50 PM

#

https://github.com/RTippin/messenger/blob/1.x/database/migrations/2019_10_01_000026_create_messages_table.php#L28C12-L28C35
https://github.com/RTippin/messenger/blob/1.x/src/Models/Message.php#L128

stray star Sep 5, 2025, 10:45 AM

#

I think it would much easier to add a "type" column to plays. Then you could simply sort by id, where type != 'manual' or whatever.

forest meadow Sep 5, 2025, 2:03 PM

#

stray star I think it would much easier to add a "type" column to plays. Then you could sim...

i dont think so. lets say a user played 4 songs:

song 1, 15:00h - play id 1
song 2, 15:05h - play id 2
song 3, 15:15h - play id 3

then the user says, i listened a song at 15:07h. so it creates a new Play record manually:
song 4, 15:07h - play id 4

how u sort that by id??

#

1, 2, 3, 4

#

but its actually 1, 2, 4, 3

#

by played_at

lost bolt Sep 5, 2025, 2:10 PM

#

forest meadow if i dont use the mysql grammar, it doesnt work im testing it like this: ```ph...

Your example reuses the same now() though?

forest meadow Sep 5, 2025, 2:10 PM

#

lost bolt Your example reuses the same now() though?

#1413227873648775321 message

#

i add microseconds

#

in booted creating

lost bolt Sep 5, 2025, 2:12 PM

#

You should define the entire models storage date format, not within the cast like that. Which also means, IIRC, that you'd want all timestamp columns for that model to use precision then (including created/updated)

#

See where Evan linked above, shows the model using protected $dateFormat

forest meadow Sep 5, 2025, 2:14 PM

#

why should all the timestamps in the table follow the same precision?

lost bolt Sep 5, 2025, 2:15 PM

#

To be honest I only ever needed precision timestamps when I made my messenger, and the docs show that property as the way to override the storage for all datetime on the model, so I just used it for all timestamps columns myself

forest meadow Sep 5, 2025, 2:17 PM

#

from my ignorance on the topic, i feel is useless to store precision in created_at and updated_at if i dont need it on those fields, no?

lost bolt Sep 5, 2025, 2:18 PM

#

I also think it would just be ignored if you dont have the precision set on the DB lvl

#

So I'd try setting that property on the model and FAFO

#

Just be sure your played at uses precision in migration

forest meadow Sep 5, 2025, 2:19 PM

#

forest meadow just now, ive also make it so the played_at is unique. in case it isnt, to preve...

then im good, despite this booted creating "thing", where i enseure played_at is unique. if i comment out the booted fn and i use the protected $dateFormat, it works fine if the played_at is unique, otherwise db crashes.

forest meadow Sep 5, 2025, 2:20 PM

#

lost bolt Just be sure your played at uses precision in migration

ye it does

lost bolt Sep 5, 2025, 2:21 PM

#

Db crashes if not unique? What

forest meadow Sep 5, 2025, 2:21 PM

#

so setting a query grammar in boot appserviceprovider:

    public function boot(): void
    {
        DB::connection()->setQueryGrammar(new \App\Database\Query\Grammars\MySqlGrammar(DB::connection()));
    }

<?php

namespace App\Database\Query\Grammars;

class MySqlGrammar extends \Illuminate\Database\Query\Grammars\MySqlGrammar
{
    public function getDateFormat(): string
    {
        return 'Y-m-d H:i:s.u';
    }
}

makes it that it works

forest meadow Sep 5, 2025, 2:21 PM

#

lost bolt Db crashes if not unique? What

yeah if i do this:

$now = now();
Play::factory()->count(1)->create([
    'played_at' => $now,
]);
Play::factory()->count(1)->create([
    'played_at' => $now,
    'song_id' => 3,
]);

as the played_at is unique, it crashes

#

cuz i have set a unique index obviously for that field

lost bolt Sep 5, 2025, 2:23 PM

#

Welll yeah, didn't realize you set a unique index. I see zero reason for that. Index yes, unique no. It's a timestamp. And if it was truly using atomic time, you wouldn't see a duplicate (in my messenger I could seed tens of thousands of records in a couple seconds with factories and not one would have the same timestamp )

#

Reusing the same carbon instance obviously will, but if you dont do that and its using precision, that won't really happen

forest meadow Sep 5, 2025, 2:31 PM

#

okay im thinking about what u say about not setting unique index

but dont u think is more robust using it as unique? this way we ensure that is impossible to have 2 plays with same played_at, so the order is perfectly ordered without any issues?

or on the other hand u think that the developers should take care when coding and make it so duplicated played_at fields dont occur?

lost bolt Sep 5, 2025, 2:33 PM

#

Unique to me is reserved for contexts in which a value truly cannot be reused due to data integrity. A datetime will never fit that context in my view, not to mention if using precision 6, I've yet to ever notice a collision myself. I see this as a simple way to ensure you can sort records in an absolute order when you expect to sort via a timestamp and know those timestamps could be created within the same second

forest meadow Sep 5, 2025, 2:37 PM

#

im removing the unique then i guess haha ( :

#

now imagine a user plays a song now, but just plays it for 2 seconds. then leaves. tomorrow comes back and plays that play - the full song.

for u, when the user really played the song at?

#

right now, would be the first time that the play is created

#

but if the user really plays it tomorrow then.... hmmmm

#

i know this is not the original question on this thread, if i have to, i can ask it in a new thread

#

but as is related to the same model and so, i ask it here if its ok

lost bolt Sep 5, 2025, 2:40 PM

#

I mean, that's where you could argue the created at having precision is useful. They may create 3 records in one second, and on any subsequent replays, the played at would be updated

#

Then you'd be covered from any angle

#

But then it's more of a "last_played_at"

#

Unless you make a play record for every new play

forest meadow Sep 5, 2025, 2:45 PM

#

lost bolt I mean, that's where you could argue the created at having precision is useful. ...

but the created_at and updated_at just mean that, when the record was created and the last updated

i think we cannot use them to sort in this case

i feel they are just informative, we cannot use them for other reason in this context

havent told it to make the initial question simpler, but now that we are digging lil bit deeper, i actually have another field called actually_played_at which stores really when the user played it. it's a trigger in the app when it played like 50% of the song - configurable. so, then, actually_played_at is set. and i know when the user did put the song on queue - played_at, and when that play is actually played - actually_played_at - not sure if makes sense for you

forest meadow Sep 5, 2025, 2:45 PM

#

lost bolt Unless you make a play record for every new play

i think we should make a new record for every new play

#

to keep them all, not only last_played_at i guess

forest meadow Sep 5, 2025, 2:45 PM

#

forest meadow but the created_at and updated_at just mean that, when the record was created an...

if something is unclear let me know please im trying to explain it as clear as i can

lost bolt Sep 5, 2025, 2:49 PM

#

Then it sounds like created at being precision is all you need, if you'd consider a play the second the record is made, and only care about updating the other field after 50% playthrough while not needing it for sorting.

forest meadow Sep 5, 2025, 2:50 PM

#

if you'd consider a play the second the record is made
i consider a play the second the record is made via the player. if is made via other view in the app where user can register plays manually, then no

#

since right now, a user can create a play from a song listened last week at 23:00h on sunday

#

thats why i said ids, created_at and updated at are only informative imo in this case, cannot be used to sort anything about the player plays order

lost bolt Sep 5, 2025, 3:00 PM

#

That's fair.

#

Considering I have not used precision in a couple years, I have not tested if there are better ways to utilize queries, but at the time, I had my own "cursor" pagination of sorts, and I had to format the date in my query to make use of precision

#Should I Modify The Timestamp Precision in the Database?