Difference between revisions of "Freeside:1.7:Documentation:Dates"
From Freeside
(→Conversion in Crystal Reports) |
(→Conversion in PostgreSQL) |
||
| (2 intermediate revisions by the same user not shown) | |||
| Line 11: | Line 11: | ||
== Conversion in PostgreSQL == | == Conversion in PostgreSQL == | ||
| − | * Conversion from UNIX time (substitute in your own time zone, date field and output field name): <pre><nowiki> | + | * Conversion from UNIX time (substitute in your own time zone, date field and output field name): |
| + | <pre><nowiki> | ||
'epoch'::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'PST' + _date_field * '1 second'::INTERVAL AS output_fieldname | 'epoch'::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'PST' + _date_field * '1 second'::INTERVAL AS output_fieldname | ||
| + | </nowiki></pre> | ||
| + | |||
| + | * Conversion to UNIX time: | ||
| + | <pre><nowiki> | ||
| + | EXTRACT( EPOCH FROM date_column ) | ||
| + | </nowiki></pre> | ||
| + | |||
| + | == Conversion in MySQL == | ||
| + | |||
| + | * Conversion to UNIX time: | ||
| + | <pre><nowiki> | ||
| + | UNIX_TIMESTAMP( date_column ) | ||
</nowiki></pre> | </nowiki></pre> | ||
Latest revision as of 18:08, 3 December 2007
Contents
Introduction
Freeside stores most dates as "Unix time" - the number of seconds elapsed since midnight UTC on the morning of January 1, 1970, not counting leap seconds.
Conversion in Perl
- Conversion from UNIX time: see the
localtimefunction or theDate::Formatmodule.
- Conversion to UNIX time: see the
Date::ParseorTime::Localmodules, among others.
Conversion in PostgreSQL
- Conversion from UNIX time (substitute in your own time zone, date field and output field name):
'epoch'::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'PST' + _date_field * '1 second'::INTERVAL AS output_fieldname
- Conversion to UNIX time:
EXTRACT( EPOCH FROM date_column )
Conversion in MySQL
- Conversion to UNIX time:
UNIX_TIMESTAMP( date_column )
Conversion in Crystal Reports
- Download the U2lwin32 user function library
- Use the provided "EpochToDateTime" function
- Works with Crystal Reports 11 by manually installing the U2lwin32.dll to Business Objects\Common\3.5\bin
