Datetime to unix seconds?

Technical Help
1

The default date format seem to allow the use of PostgreSQL formatting rules, like datetime_format(field('<your-field>'), '<your-format>'), where <your-format> is anything supported by PostgreSQL, as described here: PostgreSQL TO_CHAR Function By Practical Examples

A problem though, is that it doesn’t support direct conversion to Unix Seconds, useful in many computations. The closest conversion I found, is go by proxy of Julian days (J):

86400*minus(tonumber(datetime_format(field('<your-field>'), 'J')) + minus(tonumber(datetime_format(field('<your-field>'), 'SSSSS')), 86400/2)/86400, 2440587.5)

Any shorter or more elegant conversion to Unix Seconds would be desired.

1 Like
2

Hi @Mindey ,

Does the date_diff function solves the issue? I tried the following formula:
date_diff(‘ss’, todate(‘1970-01-01’, ‘YYYY-MM-DD’), field(‘Date’))

I simply calculate the difference between start of the unix timestamp (1-1-1970) and the value of the field containing my date. Setting the format to seconds (ss) return the unix timestamp in seconds.

1 Like
3

@frederikdc date_diff definitely looks better, and seem to work! Didn’t know it returns the diff in seconds.

4

Just quoting you with corrected quotation marks, easier for copy-pasting:

Date to Unix seconds:

date_diff('ss', todate('1970-01-01', 'YYYY-MM-DD'), field('Date'))
1 Like