I have a table that has a few thousand records in it. In that table is a field with a timestamp. What I have been asked to do is to break that data down into timeframes.
For example; how many records are timestamped between 08:00 and 09:00. Is there a way in Wappler to convert/format timestamps to hours and filter it?
Hi Brad,
UNIX timestamp is the time in seconds since Jan 01 1970 in Coordinated Universal Time (UTC).
You can convert the timestamp values to HH:mm format, like: {{your_value.formatDate("HH:mm")}} which will return the time part of the timestamp.
If you need the whole date in a human readable format then you can format it like: {{your_value.formatDate("yyyy-MM-dd HH:mm")}} etc. using the server data formatter.
Note that the time will be in UTC time zone, so if you want to check with your time zone, you will probably need to adjust it according your time zone.
Thanks Teodor, I am full aware on how to format the date and time. My issue is filtering by time. For example filtering out all the records that happen during a specified timeframe. Also for example I need to pull all the records that were in the month of August between 8:00am and 9:00am
I believe in your case it would be the best to use the custom query option and use a custom sql query to achieve what you need.
Maybe something like this, not sure about the timezones differences here and whether you like to check utc time or not:
SELECT *
FROM yourtable
WHERE DATE_FORMAT(FROM_UNIXTIME(yourtable.timestamp_column), '%H') BETWEEN 8 AND 9
You can try this in a custom query and adjust according to your needs.