Datasets: Unix Timestamp

[Arnold_Naber]

Hi,

I have a data set that gets information about teachers that arent available. I get the firstname, prefix, lastname, ID, start and end date. when I test the connection.

I want to only show the unavailable teachers of the specific day.
"start"and “end” is the date value in Unix Timestamp but i cant seem to choose that option with the date format when i setup the colomn.

[ProServ]

Hi @Arnold_Naber

Here we can see that the columns can be given a formula:

Dataset DateTime - can i show only time/date

Hi, You could create a formula column and set the formula to $dateFormat(colWithDateIn,d-m-Y) where the colWithDateIn is the name of the column that contains the full date. d-m-Y can be any PHP date format (https://xibo.org.uk/docs/setup/date-format-php) I hope that helps Thanks, Dan

I had done something like this with SQL queries but I never converted a timestamp.

Please, let us know if you find the right formula.

[Arnold_Naber]

Ah thanks !

I made a new columns with a number, and created the folowing formula that seems to work.

$dateFormat((FROM_UNIXTIME(UTdate),d-M)

I now get 8-apri. i would rather have 8 April but this is ok for now. I only need it to sort the results to the current day so i need to figure that out next.

[ProServ]

@Arnold_Naber

I now get 8-apri. i would rather have 8 April

Try with:

$dateFormat((FROM_UNIXTIME(UTdate),d F)

https://www.php.net/manual/en/datetime.format.php

@Arnold_Naber

I only need it to sort the results to the current day so i need to figure that out next.

May be a solution to your needs:

Filter today's events in dataset

Hi, Finally got the advanced filter to work for showing only today’s events from a dataset. I used the following query where Starts is the event starting date. DATE_FORMAT(NOW(), ‘%Y-%m-%d 00:00:01’) < Starts AND Starts < DATE_FORMAT(NOW(), ‘%Y-%m-%d 23:59:59’) May help someone. CMS v1.8.7

or:

Dataset API - Filter row on Dates

Would someone be able to post an example of filtering a Dataset’s Data based on a date value? It’s easy to filter on a single column that is a String Value. The example below works: /api/dataset/data/1?Location=Room 1 However the same format above does not work for a date field. I want to query the API for dataset results where the date column that falls within a specific date range.