troubleshooting Question

Remove timezone from SQL JSON string

Avatar of Richard Lloyd
Richard Lloyd asked on
Microsoft SQL ServerJSONSQL
3 Comments1 Solution220 ViewsLast Modified:
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);

?>

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"
    }
  }
]

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"
  }
]

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
ASKER CERTIFIED SOLUTION
Richard Lloyd

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros