Difference between revisions of "Freeside:1.7:Documentation:Dates"

From Freeside
Jump to: navigation, search
(→‎Conversion in PostgreSQL)
 
(3 intermediate revisions by 2 users 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>
  
Line 19: Line 32:
 
* Download the [http://www.viksoe.dk/code/u2lwin32.htm U2lwin32 user function library]
 
* Download the [http://www.viksoe.dk/code/u2lwin32.htm U2lwin32 user function library]
 
* Use the provided "EpochToDateTime" function
 
* Use the provided "EpochToDateTime" function
−
* Note: Untested. If it works for you, edit the page and remove this note.
+
* Works with Crystal Reports 11 by manually installing the U2lwin32.dll to Business Objects\Common\3.5\bin

Latest revision as of 17:08, 3 December 2007

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 localtime function or the Date::Format module.
  • Conversion to UNIX time: see the Date::Parse or Time::Local modules, 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
Retrieved from "http://freeside.biz/mediawiki/index.php?title=Freeside:1.7:Documentation:Dates&oldid=3090"