Avatar of Richard Lloyd
Richard Lloyd
 asked on

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

Microsoft SQL ServerJSONSQL

Avatar of undefined
Last Comment
Richard Lloyd

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Richard Lloyd

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Olaf Doschke

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.
Richard Lloyd

ASKER
Worked this one out myself!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy