-3

I am querying an API and I got a date like this: "2019-04-17T14:04:24.224-04:00". I suppose this is an iso date. How can I store the year, month and day on a mysql database? I want the following format "dd/mm/yyyy".

Diego Alves
  • 2,462
  • 3
  • 32
  • 65

3 Answers3

1

Formats are a display concern, that is it's a function of who is looking at the data, so you don't do it until you know who's looking at it. At the point of display you apply the format required by the user's locale, and quite often adjust for their local time-zone as well.

MySQL's DATE column type is expressed in ISO-8601 format like you have, so you should be able to insert that minus everything after the "T".

So either strip that out before insertion and it should be fine.

On display you can use any of the PHP date formatting functions to get the exact format you want. This is often locale specific.

You'll want to be sure that the time expressed is in the correct time-zone. Do any conversion necessary to get it in the right zone before converting to a date or you may find you're getting the wrong date for several hours of the day.

Generally you should:

  • Store dates as ISO-8601 formatted values in MySQL's native DATE or DATETIME fields and convert to/from localized formats as dictated by user preferences.
  • Store any time values as UTC and convert to/from local time as dictated by user preferences.
tadman
  • 208,517
  • 23
  • 234
  • 262
0
date("d-m-Y", strtotime("2019-04-17T14:04:24.224-04:00"));

Just do this,

Lulceltech
  • 1,662
  • 11
  • 22
  • 1
    Don't do this. MySQL prefers ISO-8601 dates in YYYY-MM-DD format. – tadman Apr 12 '19 at 18:53
  • You're right however he asked for it in a specific format so I gave it in that format – Lulceltech Apr 12 '19 at 18:55
  • MySQL's `DATE` column won't handle ambiguous values like `DD-MM-YYYY` though. – tadman Apr 12 '19 at 18:58
  • Then store it in varchar. If he want's it in the specific format and stored like that it's the only option... – Lulceltech Apr 12 '19 at 19:05
  • 1
    I'm suggesting two things here: A) `DD-MM-YYYY` is a localization that means nothing to your database and doesn't belong in the database in the first place. B) That you convert if/when necessary to whatever localization format the user prefers. Maybe they want `MM-DD-YYYY` or `Mmm dd, YYYY`, you don't know. That's why you save that for later. ISO dates can be indexed for ranges and computed on, this oddball format can't. – tadman Apr 12 '19 at 19:07
  • I didn't say I don't agree that it should be formatted in Y-m-d, that's how I would do it personally as it's standard then just change how you display it on the front end. What i'm saying is for what he want's that is the only way to accomplish it. As long as he's not modifying or comparing dates in sql it won't be an issue. – Lulceltech Apr 12 '19 at 19:09
-1

Try this! 

update TABLENAME 
set date_columnname = LEFT(date_present_columnname,10)
terry
  • 143
  • 13