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

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"), ); }

rwlloyd71Author Commented: 2018-04-11

Update.

I have fixed the problem by using

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

so the whole array is created by :

LVL 31

Olaf DoschkeSoftware DeveloperCommented: 2018-04-11

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.

rwlloyd71Author Commented: 2018-04-20

Worked this one out myself!

Remove timezone from SQL JSON string

Who is Participating?

Already a member? Login.