Xano - Timestamps

In Xano, timestamps are stored as Unix timestamps in milliseconds, representing the number of milliseconds since January 1st, 1970 UTC. However, the timestamp displayed in the database table or spreadsheet view is transformed to match the time zone of your viewer's browser. This means that if you're in California, you'll see a different view of the timestamp than someone in New York.

Xano accepts inputted timestamps in various formats, including raw Unix timestamps in milliseconds, ISO 8601 format, PostgreSQL database format, and relative time format according to php.net. You can find all the details about these formats in the Xano documentation.

Using Timestamp Filters

Xano provides several timestamp filters that allow you to manipulate and format timestamps according to your needs. Let's go through each filter and see how it works.

Converting Text to Timestamp

If you want to convert text to a timestamp, you can use the `to timestamp` filter under the "Conversion" category. This filter requires you to specify the time zone you want to use. Xano provides a list of all available time zones, which you can find in the documentation (with a link to the Wikipedia page).

For example, if you enter "last Monday" in the text field and use the `to timestamp` filter with the "America/Los Angeles" time zone, Xano will convert "last Monday" to a Unix timestamp in milliseconds based on the specified time zone.

It's important to note that when using relative times like "last Monday," the time zone becomes relevant because "last Monday" for someone in California will be a different Unix timestamp than someone in New York. However, if you use "now," the time zone becomes irrelevant since "now" is the same Unix timestamp for everyone in the world.

Formatting Timestamps

To format a timestamp into a human-readable format, you can use the `format timestamp` filter under the "Timestamp" category. This filter allows you to specify the date/time format using PHP's date/time format characters, which you can find in the documentation (with a link to the PHP documentation).

For example, if you have a Unix timestamp and want to display it in a human-readable format like "Tuesday, 10th of November 2020, 4:37 PM," you can use the `format timestamp` filter with the format character `r` (lowercase) and specify the "America/Los Angeles" time zone.

Transforming Timestamps

The `transform timestamp` filter under the "Timestamp" category allows you to apply transformations to a timestamp based on a previous time. You can add or subtract a specific number of days, hours, minutes, or seconds to the timestamp.

For instance, if you have a timestamp representing "last Monday" and want to find the timestamp for seven days after that, you can use the `transform timestamp` filter and specify "+7 days" as the transformation. You'll also need to provide the time zone for the transformation.

Adding Milliseconds or Seconds to Timestamps

Xano also provides filters to add milliseconds (`add milliseconds to timestamp`) or seconds (`add seconds to timestamp`) to a Unix timestamp. These filters are straightforward — you provide an integer value representing the number of milliseconds or seconds to add to the timestamp.

For example, if you have a timestamp for "last Monday" and want to add 600 seconds (10 minutes) to it, you can use the `add seconds to timestamp` filter with the value 600.

Conclusion

Working with timestamps in Xano is made easy with the provided filters. You can convert text to timestamps, format timestamps into human-readable formats, transform timestamps based on a previous time, and add milliseconds or seconds to timestamps. All the necessary information, including accepted timestamp formats and time zone references, is available in the Xano documentation.

Remember, when dealing with relative times like "last Monday," the time zone becomes crucial, as the same relative time can represent different Unix timestamps for different locations. However, for absolute times like "now," the time zone is irrelevant since "now" is the same Unix timestamp for everyone.

By following the step-by-step instructions and examples provided, you can confidently manipulate and work with timestamps in your Xano projects, making it easier to handle date and time-related data.