timezone and date/time format of cmn_schedule_span

[divvi_vamsi]

What is the timezone and date/time format and field type of start_date_time field in the cmn_schedule_span table?

The time format seems to be a ISO format, but few values show 'Z' in the end while few records don't.

The time zone of my instance is MDT and i understand while displaying the value it does in MDT but while storing the values Servicenow does it in UTC format; but while querying through the records through a glide filter which format should we be using.

Thanks

Divya

[tim210]

Hi Divya,

Date/time fields in ServiceNow (e.g. Created[sys_created_on] or Update[sys_updated_on]) are stored in the database in UTC, and when you access them through ServiceNow the application displays the date/time in whatever timezone you've set (the 'display value').   But the start_date_time and end_date_time on cmn_schedule_span are not actually date/time fields, they're stored in the database as strings:

$ sudo snow query <instance> "show columns from cmn_schedule_span"

+------+---------------------+-------------+------+-----+---------+-------+

| Port | Field                           | Type               | Null | Key | Default | Extra |

+------+---------------------+-------------+------+-----+---------+-------+

| 3518 | schedule                       | varchar(32) | YES   | MUL |               |           |

| 3518 | name                               | varchar(80) | YES   |       |               |           |

| 3518 | notes                           | mediumtext   | YES   |       |               |           |

| 3518 | sys_class_name           | varchar(80) | YES   |       |               |           |

| 3518 | show_as                       | varchar(40) | YES   |       |               |           |

| 3518 | type                               | varchar(40) | YES   |       |               |           |

| 3518 | start_date_time       | varchar(40) | YES   |       |               |           |

| 3518 | end_date_time           | varchar(40) | YES   |       |               |           |

Looking at the format, there's no timezone defined, so it's probably seen as being in whatever timezone is currently in use on the instance. E.g. Christmas Day is defined as start_date_time 20121225T000000 end_date_time 20121225T235959.

Let me know if you need more information, or if you can provide more details of how you're trying to use these fields I might be able to help more.

[Zach Wehrli]

Check my replies on these posts for the solutions I came up with.

What is the correct method to query Schedule Date/time fields in cmn_schedule_span?

How to create entry in "cmn_schedule_span" table through script.