28

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'?

Mike Brant
  • 70,514
  • 10
  • 99
  • 103
html_programmer
  • 18,126
  • 18
  • 85
  • 158
  • So you need a true `0` or an empty datetime like `0000-00-00 00:00:00`? – Machavity Dec 29 '14 at 17:35
  • I need a format where I have `yyyy-mm-dd 00:00:00`. – html_programmer Dec 29 '14 at 17:37
  • 3
    Why not just use a date field rather than a datetime/timestamp field? This field type does not have time component. – Mike Brant Dec 29 '14 at 17:40
  • 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). – html_programmer Dec 29 '14 at 17:42
  • 1
    Have you tried: `Carbon::createFromFormat('d-m-Y 00:00:00', ..);` ;p – Lawrence Cherone Dec 29 '14 at 17:43
  • *Sidenote*, carbon can create the exact format your after, look at the docs – Lawrence Cherone Dec 29 '14 at 17:44
  • It didn't work for me, I'll take a closer look at the docs. – html_programmer Dec 29 '14 at 17:50
  • 2
    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. – Lawrence Cherone Dec 29 '14 at 17:50
  • 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. – html_programmer Dec 29 '14 at 17:55
  • 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. – Matt Johnson-Pint Dec 29 '14 at 18:34
  • @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.` -> http://stackoverflow.com/questions/409286/datetime-vs-timestamp – html_programmer Dec 29 '14 at 18:50
  • 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](http://dev.mysql.com/doc/refman/5.7/en/datetime.html). Next, you should realize that scheduling future events, especially recurring ones, is a complex subject. My answer [here](http://stackoverflow.com/a/19627330/634824) may help you. – Matt Johnson-Pint Dec 29 '14 at 18:58
  • 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. – Matt Johnson-Pint Dec 29 '14 at 19:00
  • @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: http://stackoverflow.com/questions/5183630/calendar-recurring-repeating-events-best-storage-method I will convert datetime to seconds to get the job done. Thanks for your help. – html_programmer Dec 29 '14 at 19:02

4 Answers4

38

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();
Abdalla Arbab
  • 1,360
  • 3
  • 23
  • 29
34

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);
}
Alister Bulman
  • 34,482
  • 9
  • 71
  • 110
  • As of v1.24.0, the Carbon library has also added `createMidnightDate($year = null, $month = null, $day = null, $tz = null)`. – Alister Bulman Mar 09 '18 at 21:08
  • In case we have a `time` field, we can set that directly calling `setTimeFromTimeString` from the Carbon instance. – JCarlosR Sep 09 '20 at 18:22
1

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
);
HMD
  • 2,202
  • 6
  • 24
  • 37
Reto Kohli
  • 21
  • 3
0

You shouldn't do that.

set your start_date DATE NULL DEFAULT NULL`

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

unixmiah
  • 3,081
  • 1
  • 12
  • 26