S-SQL Examples D

Remember the test table from the introduction? It has a timestamp with timezone field called "date", a numeric field called "number-test" and a money field called "money".

Lisp has a ratio datatype. In other words, instead of trying to come up with a floating point number for 1/3, common lisp simply views it as 1/3. But how does that translate into storage in postgresql?

(query (:insert-into 'test :set 'id 1 'number-test (/ 1 3) 'text "one third"))

(query (:select 'number-test :from 'test :where (:= 'id 1)) :single)

3333333333333333333333333333333333333/10000000000000000000000000000000000000

We inserted a ratio 1/3 into the numeric field, then got something back that was not quite the same, but is decimal ratio truncated at 37 decimal points.

Now insert that same ratio into the money field.

(query (:insert-into 'test :set 'id 1 'money (/ 1 3) 'text "one third money"))

(query (:select 'money :from 'test :where (:= 'id 1)) :single)

"$0.33"

Here you notice that you got back a string that looks like a number truncated at two decimal points (the pennies). You would have to convert it to something else in order to perform any mathematical calculations.

So, for example, you might reach for the wu-decimal package or the decimals package and call wu-decimal:parse-decimal or decimals:parse-decimal-number like so:

(wu-decimal:parse-decimal "$0.33" :start 1)

33/100

Notice that we tried to start at 1 in order to get rid of the monetary indicator at the front. However, that doesn't work if the number was negative. Better is just removing the monetary indicator.

(wu-decimal:parse-decimal (remove #\$ "-$0.33"))

-33/100

or, using the decimals package:

(decimals:parse-decimal-number (remove #\$ "-$0.33"))

-33/100

Postgresql keeps everything in a single timezone - UTC. Then everything else is set using the offset. See, e.g. http://www.postgresql.org/docs/current/datatype-datetime.html

"For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the timezone zone.

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct."

So, looking at a server that is set for PDT, for table test with fields id, date, number_test, money and text

(query (:insert-into 'test :set 'id 3 'text "insert here"
                     'date (local-time:encode-timestamp 0 0 0 12 01 01 2013)))

2013-01-01 12:00:00-08

(looking at the default timezone for the server, postgresql has set the timezone to UTC less 8 hours - UTC time would be 04:00:00)

Using offset to explicitly offset 1 hour from UTC (e.g. Paris)

(query (:insert-into 'test
        :set 'id 4 'text "offset 1 hour"
                     'date (local-time:encode-timestamp 0 0 0 12 01 01 2013
                                                        :offset 3600)))

2013-01-01 03:00:00-08

(looking at the default timezone for the server, postgresql has kept the timezone as PDT - UTC less 8 hours - but set the time as 03:00:00, which is 1 hour ahead of UTC)

Using timezone to explicitly set it for UTC

(query (:insert-into 'test
        :set 'id 5 'text "insert here using timezone utc"
                     'date (local-time:encode-timestamp 0 0 0 12 01 01 2013
                                                        :timezone local-time::+utc-zone+)))

2013-01-01 04:00:00-08

(looking at the default timezone for the server, postgresql has kept the timezone as PDT - UTC less 8 hours - but set the time as 04:00:00, which is the time in UTC relative to the PDT time at the server.

See Time Functions for information on specific functions dealing with time.

Personally I like using cl-postgres-datetime. Why? cl-postgres-datetime provides date/time integration for cl-postgres. It uses local-time for types that use time zones (i.e. timestamptz) and simple-date for types that don't (i.e. timestamp, date, time, interval).