Thread: Trouble with UNIX TimeStamps
Hi Guys,
I'm new to PostGreSQL functions, in particular the date/time functions. I'm
porting my application away from MySQL
The query I'm having trouble converting a mySQL query that looks like this:
SELECT count(*) AS total,
DAYNAME(FROM_UNIXTIME(ticket_starters.ticket_time_start)) AS day FROM
ticket_queues
LEFT JOIN ticket_techs ON ticket_techs.queue_id = ticket_queues.queue_id
LEFT JOIN ticket_starters ON ticket_starters.queue_id =
ticket_techs.queue_id
WHERE
AND ticket_starters.ticket_time_start > '".(time()-(60*60*24*365))."'
GROUP BY day, total
The table column, ticket_starters.ticket_time_start is an INT type which
contains a UNIX timestamp. I did not see anywhere in the Interactive docs
how'd I convert the UNIX timestamp into a timestamp type so I can extract
the day name. I'd rather not use PostGreSQL's timestamp types and just
convert the database over to it since much of the programming utilizes the
UNIX timestamp. Any ideas?
Regards,
Jonathan Chum
Systems Developer
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
A I S M e d i a , I n c .
"We Build eBusinesses"
115 Perimeter Center Terrace
Suite 540
Atlanta, GA 30346
Tel: 800.784.0919, Ext 502 / Fax: 678.382.2471
http://www.aismedia.com / jchum@aismedia.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.427 / Virus Database: 240 - Release Date: 12/6/02
On Fri, Dec 27, 2002 at 08:49:58 -0500,
Jonathan Chum <jchum@aismedia.com> wrote:
>
> The table column, ticket_starters.ticket_time_start is an INT type which
> contains a UNIX timestamp. I did not see anywhere in the Interactive docs
> how'd I convert the UNIX timestamp into a timestamp type so I can extract
> the day name. I'd rather not use PostGreSQL's timestamp types and just
> convert the database over to it since much of the programming utilizes the
> UNIX timestamp. Any ideas?
One way to do this is:
area=> select to_char('epoch'::timestamp + (1040999196 || ' seconds')::interval,'Day');
to_char
-----------
Friday
(1 row)
Sorry, I'm still to new with using functions within PostGreSQL, but . . . I've tried: SELECT to_char((1040999196 || ' seconds')::interval + ticket_starters.ticket_time_start::timestamptz,'Day') FROM ticket_starters; and it returned back: ERROR: Cannot cast type integer to timestamp with time zone So constructed antoher query: SELECT to_char((1040999196 || ' seconds')::interval + ticket_starters.ticket_time_start::timestamp ,'Day') FROM ticket_starters; and it returned back: ERROR: Cannot cast type integer to timestamp without time zone In my table, the column, ticket_time_start has a INTEGER value of '1009462540' which is today's date. How'd would I construct the query to pull from the db? Regards, Jonathan Chum Systems Developer ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ A I S M e d i a , I n c . "We Build eBusinesses" 115 Perimeter Center Terrace Suite 540 Atlanta, GA 30346 Tel: 800.784.0919, Ext 502 / Fax: 678.382.2471 http://www.aismedia.com / jchum@aismedia.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Bruno Wolff III Sent: Friday, December 27, 2002 9:38 AM To: Jonathan Chum Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Trouble with UNIX TimeStamps On Fri, Dec 27, 2002 at 08:49:58 -0500, Jonathan Chum <jchum@aismedia.com> wrote: > > The table column, ticket_starters.ticket_time_start is an INT type which > contains a UNIX timestamp. I did not see anywhere in the Interactive docs > how'd I convert the UNIX timestamp into a timestamp type so I can extract > the day name. I'd rather not use PostGreSQL's timestamp types and just > convert the database over to it since much of the programming utilizes the > UNIX timestamp. Any ideas? One way to do this is: area=> select to_char('epoch'::timestamp + (1040999196 || ' seconds')::interval,'Day'); to_char ----------- Friday (1 row) ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.427 / Virus Database: 240 - Release Date: 12/6/02 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.427 / Virus Database: 240 - Release Date: 12/6/02
On Fri, Dec 27, 2002 at 09:58:14 -0500,
Jonathan Chum <jchum@aismedia.com> wrote:
> Sorry, I'm still to new with using functions within PostGreSQL, but . . .
> I've tried:
>
> SELECT to_char((1040999196 || ' seconds')::interval +
> ticket_starters.ticket_time_start::timestamptz,'Day') FROM ticket_starters;
>
> and it returned back:
>
> ERROR: Cannot cast type integer to timestamp with time zone
You mixed up what needed to be replaced in the example. Try something like:
SELECT to_char('epoch'::timestamp + (ticket_starters.ticket_time_start ||
' seconds')::interval, 'Day') FROM ticket_starters;
I haven't tested this example so I may have made a typo.
What this is doing is using the to_char function to print the day of the
week corresponding to the calculated timestamp.
Since what you have is an integer offset from the unix epoch. I add the
offset to the timestamp corresponding to the epoch to get the desired
timestamp. In 7.3 there isn't an integer to interval conversion function
(there may have been one earlier that assumed the integer was the number
of seconds), so I build a text string suitable for converting to interval.
Since unix time returns seconds from the epoch, I specify that the number
used for the interval is in seconds.
> -----Original Message-----
> Sent: Friday, December 27, 2002 9:38 AM
>
> One way to do this is:
> area=> select to_char('epoch'::timestamp + (1040999196 || '
> seconds')::interval,'Day');
> to_char
> -----------
> Friday
> (1 row)
On Fri, Dec 27, 2002 at 09:58:14AM -0500, Jonathan Chum wrote:
> Sorry, I'm still to new with using functions within PostGreSQL, but . . .
> SELECT to_char((1040999196 || ' seconds')::interval +
> ticket_starters.ticket_time_start::timestamp ,'Day') FROM ticket_starters;
>
> and it returned back:
>
> ERROR: Cannot cast type integer to timestamp without time zone
>
> In my table, the column, ticket_time_start has a INTEGER value of
> '1009462540' which is today's date.
>
> How'd would I construct the query to pull from the db?
SELECT
to_char('epoch'::timestamp +
(a_table.unix_seconds_field || ' seconds')::interval,
'Day')
WHERE
some.condition > particular.thingie
;
i think...
--
"We will fight them on the beaches, we will fight them on the
sons of beaches" -- Miguel Churchill, Winston's bastard Mexican
brother.
--lifted from http://www.astray.com/acmemail/stable/documentation.xml
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
Another way
You could replace
AND ticket_starters.ticket_time_start > '".(time()-(60*60*24*365))."'
with
AND ticket_starters.ticket_time_start > date_part('epoch', now())
-(60*60*24*365)
I did a search and found this solution on this page
http://www.archonet.com/pgdocs/date-to-epoch.html
Regards,
Simon
Jonathan Chum wrote:
>Hi Guys,
>
>I'm new to PostGreSQL functions, in particular the date/time functions. I'm
>porting my application away from MySQL
>
>The query I'm having trouble converting a mySQL query that looks like this:
>
>SELECT count(*) AS total,
>DAYNAME(FROM_UNIXTIME(ticket_starters.ticket_time_start)) AS day FROM
>ticket_queues
> LEFT JOIN ticket_techs ON ticket_techs.queue_id = ticket_queues.queue_id
> LEFT JOIN ticket_starters ON ticket_starters.queue_id =
>ticket_techs.queue_id
> WHERE
> AND ticket_starters.ticket_time_start > '".(time()-(60*60*24*365))."'
> GROUP BY day, total
>
>The table column, ticket_starters.ticket_time_start is an INT type which
>contains a UNIX timestamp. I did not see anywhere in the Interactive docs
>how'd I convert the UNIX timestamp into a timestamp type so I can extract
>the day name. I'd rather not use PostGreSQL's timestamp types and just
>convert the database over to it since much of the programming utilizes the
>UNIX timestamp. Any ideas?
>
>Regards,
>Jonathan Chum
>Systems Developer
>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>A I S M e d i a , I n c .
>"We Build eBusinesses"
>115 Perimeter Center Terrace
>Suite 540
>Atlanta, GA 30346
>Tel: 800.784.0919, Ext 502 / Fax: 678.382.2471
>http://www.aismedia.com / jchum@aismedia.com
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>
>---
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.427 / Virus Database: 240 - Release Date: 12/6/02
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>
On Fri, 2002-12-27 at 05:49, Jonathan Chum wrote: > The table column, ticket_starters.ticket_time_start is an INT type which > contains a UNIX timestamp. I did not see anywhere in the Interactive docs > how'd I convert the UNIX timestamp into a timestamp type so I can extract > the day name. I'd rather not use PostGreSQL's timestamp types and just > convert the database over to it since much of the programming utilizes the > UNIX timestamp. Any ideas? select 1041013653::int4::abstime; will give you the timestamp from unixtime. If you just want the day, you can use to_char like so: select to_char(1041013653::int4::abstime,'dd');
"Jonathan Chum" <jchum@aismedia.com> writes:
> ERROR: Cannot cast type integer to timestamp with time zone
A solution with less notational cruft is to cast the integer to abstime:
regression=# select 1040999196::abstime;
abstime
------------------------
2002-12-27 09:26:36-05
(1 row)
regression=# select to_char(1040999196::abstime, 'Day');
to_char
-----------
Friday
(1 row)
abstime is a deprecated datatype if you believe the manual, but I
seriously doubt that we'll remove it anytime soon. Maybe when Y2038
is upon us (by which time you'd better have found another representation
for your table, anyway).
regards, tom lane
Another way
You could replace
AND ticket_starters.ticket_time_start > '".(time()-(60*60*24*365))."'
with
AND ticket_starters.ticket_time_start > date_part('epoch', now())
-(60*60*24*365)
I did a search and found this
http://www.archonet.com/pgdocs/date-to-epoch.html
Regards,
Simon
Jonathan Chum wrote:
>Hi Guys,
>
>I'm new to PostGreSQL functions, in particular the date/time functions. I'm
>porting my application away from MySQL
>
>The query I'm having trouble converting a mySQL query that looks like this:
>
>SELECT count(*) AS total,
>DAYNAME(FROM_UNIXTIME(ticket_starters.ticket_time_start)) AS day FROM
>ticket_queues
> LEFT JOIN ticket_techs ON ticket_techs.queue_id = ticket_queues.queue_id
> LEFT JOIN ticket_starters ON ticket_starters.queue_id =
>ticket_techs.queue_id
> WHERE
> AND ticket_starters.ticket_time_start > '".(time()-(60*60*24*365))."'
> GROUP BY day, total
>
>The table column, ticket_starters.ticket_time_start is an INT type which
>contains a UNIX timestamp. I did not see anywhere in the Interactive docs
>how'd I convert the UNIX timestamp into a timestamp type so I can extract
>the day name. I'd rather not use PostGreSQL's timestamp types and just
>convert the database over to it since much of the programming utilizes the
>UNIX timestamp. Any ideas?
>
>Regards,
>Jonathan Chum
>Systems Developer
>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>A I S M e d i a , I n c .
>"We Build eBusinesses"
>115 Perimeter Center Terrace
>Suite 540
>Atlanta, GA 30346
>Tel: 800.784.0919, Ext 502 / Fax: 678.382.2471
>http://www.aismedia.com / jchum@aismedia.com
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>
>---
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.427 / Virus Database: 240 - Release Date: 12/6/02
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>