Store timestamp with time 00:00:00

Question

I want to store a timestamp in the database with time component as 00:00:00.
The following code:

$start_date = Carbon::createFromFormat('d-m-Y', $date_interval["start_date"]); 
$end_date = Carbon::createFromFormat('d-m-Y', $date_interval["end_date"]); 

adds the current time to the date, when I provide a date only without time specification.
I need this because I need to retrieve the internal integer from the database afterwards which should be the start of the day.

What is the best way to store this 'start of day'?

Adi

So you need a true 0 or an empty datetime like 0000-00-00 00:00:00? – Percept

I need a format where I have yyyy-mm-dd 00:00:00. – Adi

Why not just use a date field rather than a datetime/timestamp field? This field type does not have time component. – Nickens

Because, if I understood correctly, the timestamp supports a specified point in time where datetime does not. This may be important for the future in order to implement support for different time zones. Also, it appears that timestamp stores the datetime as an internal usigned integer value (which I need to make further calculation in my queries such as calculating time intervals). – Adi

Have you tried: Carbon::createFromFormat('d-m-Y 00:00:00', ..); ;p – Rovit

Sidenote, carbon can create the exact format your after, look at the docs – Rovit

It didn't work for me, I'll take a closer look at the docs. – Adi

There's also the startOfDay() method that you can use to reset the date objects time to 00:00:00, once again in docs Ctrl-F with help you find it. – Rovit

That's nice, Carbon::createFromFormat('d-m-Y', $date_interval["start_date"])->startofDay() works. If you provide it as an answer, I will accept it. – Adi

Keep in mind that not every date has a midnight in every time zone. For example, on 2014-10-19 in much of Brazil, the day started at 01:00 due to daylight saving time. The start of the day is not necessarily the best value to use to represent the date. I agree with @MikeBrant that you should just use a date field, as it implies the whole day, rather than just some point in time on that day. Of course, it completely depends on the context of what exactly you are storing. If you mean to store a specific time, then do so. Otherwise don't. Don't try to fake it. It won't help with time zones. – Naevus

@MattJohnson How do the reasons for using a timestamp fit into using the datetime datatype (see my previous comments)? If the time is stored as an internal integer value, then shouldn't the value be the same for every timezone anyway? I need this integer representation, in order to calculate recurrent events. An important difference is that DATETIME represents a date (as found in a calendar) and a time (as can be observed on a wall clock), while TIMESTAMP represents a well defined point in time. -> https://mcmap.net/q/40410/-should-i-use-the-datetime-or-timestamp-data-type-in-mysql – Adi

You should definitely not use a timestamp mysql type if the purpose is scheduling recurrent events. mysql will convert timestamp values from local time to UTC implicitly. Read these docs carefully. Next, you should realize that scheduling future events, especially recurring ones, is a complex subject. My answer here may help you. – Naevus

Regarding the quote - that is correct. Recognize that when you schedule events, you are indeed scheduling by calendar and clock - not by a well-defined instant in universal time. – Naevus

@MattJohnson Ok, switching back to datetime, just realised that indeed I need scheduling by calendar and clock. I am using a customized workflow of the answer here: #5184130 I will convert datetime to seconds to get the job done. Thanks for your help. – Adi

Answer 1

I've usually set the date with the call, and then reset the time to 00:00 with

Carbon::setTime(0, 0, 0);  // from the parent, DateTime class

In the class, there is also a Carbon::startOfDay() method that will set the appropriate values.

public function startOfDay()
{
    return $this->hour(0)->minute(0)->second(0);
}

Antonyantonym

As of v1.24.0, the Carbon library has also added createMidnightDate($year = null, $month = null, $day = null, $tz = null). – Antonyantonym

In case we have a time field, we can set that directly calling setTimeFromTimeString from the Carbon instance. – Autarchy

Answer 2

For safety just let Carbon decide the end or the start of the day using ->endOfDay() or ->startOfDay(). Choose what suits you more.

Carbon::createFromFormat('d-m-Y', $date_interval["start_date"])->endOfDay();
Carbon::createFromFormat('d-m-Y', $date_interval["start_date"])->startOfDay();

Promotion

@Adi is there any way my answer will be chosen, as it's more updated? – Promotion

Answer 3

Carbon::createFromFormat() differs from its parent, in that missing parts are set to the current date or time instead of zero. In order to make it behave like \DateTime::createFromFormat(), use the | or ! format characters, as described in the manual (https://www.php.net/manual/en/datetime.createfromformat.php).

Example:

$midnight = Carbon::createFromFormat(
    'Y-m-d|', // or: '!Y-m-d'
    $date
);

Wroughtup

This is really useful, I was never aware of this! – Commonly

Answer 4

You shouldn't do that.

set your start_date DATE NULL DEFAULT NULL`

You should let MySQL default to NULL not 0000-00-00

Durwin

I think that there is a misunderstanding... I'm not looking for a null value, rather a timestamp where the time is set to the start of the day 00-00-00. – Adi

then you should use $start_date = strtotime("midnight"); – Durwin