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

rwlloyd71Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rwlloyd71Author Commented:
Update.

I have fixed the problem by using

date_format($row["start_event"],"Y-m-d H:i:s"),

Open in new window


so the whole array is created by :

while ($row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC)) {
 $data[] = array(
  'id'   => $row["id"],
  'title'   => $row["title"],
  'start'   => date_format($row["start_event"],"Y-m-d H:i:s"),
  'end'   => date_format($row["end_event"],"Y-m-d H:i:s"),
 );
}

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olaf DoschkeSoftware DeveloperCommented:
I never saw this, interesting result, because I'd only expect that if the column data type would have been datetimeoffset instead of datetime. I also don't see any connection options for sqlsrv_connect to specify the timezone to apply to queried datetimes.

SQL Server has data types, that are not only a scalar value, some types even have functionalities attached, as long as you're on the T-SQL level, eg STDistance.

Of course, effectively you turned the datetime objects you get back from SQL Server to string type, but it should work out.
0
rwlloyd71Author Commented:
Worked this one out myself!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.