Solved: Remove timezone from SQL JSON string

I am trying to generate a json file from an SQL Server database using sqlsrv.

The code I am using is:

<?php

//load.php

include "config.php";
$data = array();

$sql = "SELECT id, title, start_event, end_event FROM events ORDER BY id";
$result=sqlsrv_query($conn, $sql);
include "error.php";
while ($row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC)) {
 $data[] = array(
  'id'   => $row["id"],
  'title'   => $row["title"],
  'start'   => $row["start_event"],
  'end'   => $row["end_event"]
 );
}

echo json_encode($data);

?>

Open in new window

The json file is created as follows:

[
  {
    "id": 1,
    "title": "Title 1",
    "start": {
      "date": "2018-04-11 18:00:00.000000",
      "timezone_type": 3,
      "timezone": "Europe/London"
    },
    "end": {
      "date": "2018-04-11 21:00:00.000000",
      "timezone_type": 3,
      "timezone": "Europe/London"
    }
  },
  {
    "id": 3,
    "title": "Title 2",
    "start": {
      "date": "2018-04-11 19:00:00.000000",
      "timezone_type": 3,
      "timezone": "Europe/London"
    },
    "end": {
      "date": "2018-04-11 21:00:00.000000",
      "timezone_type": 3,
      "timezone": "Europe/London"
    }
  },
  {
    "id": 4,
    "title": "Title 3",
    "start": {
      "date": "2018-04-11 20:00:00.000000",
      "timezone_type": 3,
      "timezone": "Europe/London"
    },
    "end": {
      "date": "2018-04-11 21:00:00.000000",
      "timezone_type": 3,
      "timezone": "Europe/London"
    }
  },
  {
    "id": 5,
    "title": "Title 4",
    "start": {
      "date": "2018-04-11 21:00:00.000000",
      "timezone_type": 3,
      "timezone": "Europe/London"
    },
    "end": {
      "date": "2018-04-11 21:00:00.000000",
      "timezone_type": 3,
      "timezone": "Europe/London"
    }
  }
]

Open in new window

How can I change the code so that it returns this:

[
  {
    "id": 1,
    "title": "Title 1",
    "start": "2018-04-11 18:00:00",
    "end": "2018-04-11 21:00:00"
  },
  {
    "id": 3,
    "title": "Title 2",
    "start": "2018-04-11 19:00:00",
    "end": "2018-04-11 21:00:00"
  },
  {
    "id": 4,
    "title": "Title 3",
    "start": "2018-04-11 20:00:00",
    "end": "2018-04-11 21:00:00"
  },
  {
    "id": 5,
    "title": "Title 4",
    "start": "2018-04-11 21:00:00",
    "end": "2018-04-11 21:00:00"
  }
]

Open in new window

My database fields are:

CREATE TABLE [dbo].[Events](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](255) NULL,
	[Start_event] [datetime] NULL,
	[End_event] [datetime] NULL,
 CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Open in new window