Unix Timestamp for deleted_at field and soft deletes

the columns are already unix timestamps. no need to store your timestamp as a string...

there should be no need to alter standard behaviour.

if you need the value back as a unix timestamp then after eloquent has converted it to a carbon instance you can ask carbon for it in timestamp format

Thank you so much for the reply, but the created_at, and updated_at columns are not timestamps, only the deleted_at is. And with all due respect, that doesn't answer my question; it only describes how I could do things differently (or perhaps "normally", in this case.)

One of the design requirements for this project is to store our datetime fields as unix timestamps in the database. Even if it wasn't a requirement, I very much prefer to do so anyway.

I do see what you mean, though, and I suppose that I could work around the deleted_at being different.

See, I have many other datetime fields in the table (that were not shown here) and they're all unix timestamps. So this work-around will mean that I'll have to treat this field differently than I do all of the other date/time fields:

• a different function in the javascript datetime pickers.
• a different validation for this field only.
• addition of Carbon where it wasn't needed before
• probably affects other areas that I can't think of right now.

Granted, all of these issues aren't a very big deal, and I could implement them without too much trouble. But it sure would be nice to have the flexibility to have all of my datetime fields to be treated similarly.

i don't really understand the issue. created_at and updated_at are timestamp column types.

why do you need to change them?

just add deleted_at also as timestamp

By default, you're absolutely correct, created_at, and updated_at are timestamps.

However, that can be changed according to the (docs)[https://laravel.com/docs/5.4/eloquent-mutators] by adding a mutator:

class Flight extends Model
{
    /**
     * The storage format of the model's date columns.
     *
     * @var string
     */
    protected $dateFormat = 'U';
}

As to why I want/need to use them, well I REALLY, REALLY hope that this does not come across as rude, but that's completely beside the point. Suffice it that the project I'm working on has a design requirement that needs to store datetimes in unix timestamp.

It doesn't matter why I need to change them; all I'm asking for is how to change the deleted_at field.

The documentation is misleading.

The $dateFormat property allows you to pass a different format to, or get a different format from, the model. It is then converted to a carbon instance and stored in the database in timestamp format.

The dateFormat tells eloquent HOW it should represent date/time fields when interacting with the outside world - not how the data is persisted in the database.

I'm not telling you that you should not use timestamp. I am telling you that timestamp is the default. You should not need to do anything.

Now, if you want to get and receive values from the model in unix timestamp format then you need to set the dateFormat (all dates will be in timestamp), or create mutators.

Remember that ordinarily, when you get created_at, updated_at or deleted_at from the model you are getting a carbon instance - which can then be formatted in any manner you want.

When you give a date time to the model it is expecting it to be in a format that can be parsed by Carbon::createFromFormat()

I am appreciative of you trying to help me, and I don't mean to be argumentative.

If I understand you right, you're describing how a default Laravel is set up; that the 3 fields we're discussing are all timestamps by default. And that the $dateFormatAnd that if I want something other than a timestamp field, that I should create a mutator. Is that approximately correct?

Although, I would disagree with you that the "$dateFormat property allows you to pass a different format to, or get a different format from, the model. "

Maybe that's true in a more general sense, and I won't argue that.

But, two paragraphs after https://laravel.com/docs/5.4/eloquent-mutators#date-mutators, there's a section titled 'Date Formats', which explicitly says "If you need to customize the timestamp format, set the $dateFormat property on your model. " And I have done that, and indeed that is reflected in my table definition (4th code paste)

With that aside, the default setup isn't what I need. The created_at and updated_at fields are working perfectly: the data for the front-end gets converted to an 'mm-dd-yy' format, and the data is stored as an integer. Just how it's supposed to be for this project.

If I hadn't wanted/needed to keep the data stored as an integer, I'd be very happy with using Laravel's defaults with all three fields stored as timestamps. But this is not the case, not in this project.

Now, I would presume that the deleted_at field would behave similarly.

But it doesn't.

Which brings me to here; to ask 'If I needed to store the deleted_at values as unix timestamps (i.e. int) in the database, how would I go about doing that?"

Again, I do appreciate you explaining to me how things are set up as default. But I need to do something a little different here.

You may be getting a duplicate because you're defining both?

$table->softDeletes();
$table->unsignedInteger('deleted_at');

If doing so, softDeletes already creates a 'deleted_at' timestamp field. You need to remove that in order to create your own new int fields.

I normally would:

$table->timestamps();
$table->softDeletes();

You use this instead:

$table->unsignedInteger('created_at');
$table->unsignedInteger('updated_at');
$table->unsignedInteger('deleted_at');

Otherwise I dont know why that migration would be failing at all?

so you want to store a timestamp value, but you don't want the database column to be TIMESTAMP, you want it to be INT ?

why does it matter if it is TIMESTAMP vs INT ?

Sorry if I seem pedantic but storing these as Int will break db related functions such as latest, sorting and filtering between dates.

I agree it should be better to store them the way Laravel supports them natively to avoid future headaches. Also, they get converted to Carbon instances automatically so you can do whatever you need with them.

Thank you both for your suggestions.

@Stratos: It's not the migration that's giving me trouble, it's the actual deletions. When I have the column set up as an INT, and when I attempt to delete a row, I get prompted with the error I described in my 4th code paste.

@Snapey: Ultimately, you're right in that it doesn't REALLY matter which format I save the datetime in the database. For this project, it's just much easier that it does so. However, you do bring up a good point in your last sentence; namely that I'd be missing out on a number of those helper functions.

I will admit that I hadn't considered that.

Now that I do, and in combination with the lack of a firm answer to my original question, it may prove easier in the long run to keep things "native." In other words, it sounds like storing those datetimes as INTs would be working "against the grain" of Laravel, so to speak.

At this point, having soft deletes, along with all of those helper functions seems like it would be more fruitful than having those dates in INT columns.

By the way, I did find this morning that someone that wrote a class to get me to where I want(ed?) to go (https://github.com/al-one/laravel-soft-deletes-unix) My initial go at it failed, but I'm no longer convinced that having those datetimes stored as INTs.

Either way, I want to thank you both for your patience, and your advice.

Soft delete is quite a simple concept, and according to the docs is a global scope, filtering records where the deleted_at is not null

In this regard, you could create your own delete method that just writes a timestamp value into the int type column?