Date and Time Transformation Overview


What Are Timestamps?

A timestamp is a “formatting string” that tells the Socrata how to interpret the characters in a source field with regard to time. A timestamp defines which part of the field’s string represents the day, month, year, hours, minutes, and seconds, as well as how it relates to local time. Timestamps are used in column transforms in the Socrata Dataset Management Experience. A transform with a timestamp might look like this: to_floating_timestamp(`column_name`,'%m/%d/%Y %I:%M:%S%P').

This formatting is made up of a list of “specifiers”, which we will refer to throughout this document. For example, the simple string %m/%d/%y specifies the month-day-year format for a date written as 07/08/01.


How to Write a Timestamp Transform

 A timestamp transformation usually references the data source and the instructions on how to interpret it.
1) to_floating_timestamp(date, text) yields a calendar date
2) to_floating_timestamp(calendar_date) -> calendar_date - just returns the date put in. This works great if your data is already in a readily readable timestamp format. Uploading a dataset with easily recognized timestamps is a good way to see how Socrata would format them - just look at the transform on the column after it is uploaded.

3) to_floating_timestamp(column, text) -> calendar_date - this is the classic transform where you enter a `column_name` and a 'formatted string of specifiers'. (The two different types of quotations characters are important.)

Example: to_floating_timestamp(`dispatch_time`, '%Y-%m-%dT%H:%M:%S%.3f')


Floating Timestamps vs. Fixed Timestamps

Floating timestamps turn a text value into a floating datetime. "Floating" means the timezone
isn't specified, ie: the time "floats" depending on where you are. This is the most commonly used timestamp. 

Fixed Timestamps represent an exact point on the time-line, encoded as ISO8601 Times, with a timezone offset. The timezone offset returned will always be expressed in UTC (Coordinated Universal Time). The timezone specifiers are:

%Z ACST Formatting only: Local time zone name

%z +0930 Offset from the local time to UTC (with UTC being +0000)

%:z +09:30 Same as %z but with a colon

Example: To convert your datetime_column from GMT to eastern time zone, try:

        to_fixed_timestamp(`datetime_column`, 'GMT'), 'US/Eastern')

More information on Timestamps can be found in these articles and links:

Timestamp Specifiers

Data Transformation Examples

How Timestamps Interact with CSV Files and Excel
Functions Involving Dates at the Socrata Developer Portal

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request



Article is closed for comments.