Working with Date and Time

 

What Are Timestamps?

A timestamp is a “formatting string” that tells the import tool 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 Data & Insights 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 the data platform 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_floating_timestamp(
        to_fixed_timestamp(`datetime_column`, 'GMT'), 'US/Eastern')

 

Relative Date Filter

Our date time columns are capable of performing relative date math with our get_utc_date function available in the Exploration Canvas. With the get_utc_date function in SoQL, every time the query is ran, the current datetime is brought into the SoQL query as a fixed timestamp displayed in Coordinated Universal Time (UTC+00:00). You can then utilize this function to filter data relative to the current date.

 

Example: If you wanted to see only values from the last 7 days, you would query:

WHERE date_diff_d(to_floating_timestamp(get_utc_date(), ‘UTC'),`datetime_column`) <= 7

 

This query uses the date_diff_d function to find the difference between the current datetime, and your date column and returns only the values less than or equal to 7.


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 Data & Insights Developer Portal

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

Comments

0 comments

Article is closed for comments.