[RESOLVED] using substring to parse filename to timestamp ?

[jara06]

Hi.

The question is fairly simple..
I have a database (pgsql 8.0), and a fairly great amount of data gets in and out all the time.
There are two important fields:
"filename" which is populated by something like "/mnt/cam/1/movie/2005/12/17/110857.avi".
"time_stamp" which defaults to now(), (of the type "timestamptz").

Until now, the default value for "time_stamp" were now(), but since it has to be 100% related to the filename (and the system has high load), I guess I need to parse the filename and put the parsed date in instead (or update if with a trigger/function).

But how ? I cant find any really usefull stuff out there and have looked at postgres.org and google too.

Please help 🙁

[devinemke]

i don't quite understand the question. you want to use the timestamp as the filename? what happens if there are two records/files entered at the exact same moment? if you want to relate database records to filenames then i really suggest you use the primary key field rather than a timestamp, that way you know each file will have a unique name.

[jara06]

Ok, I've read the post myself now, and it doesnt make any sense. .. hehe

The table has got more fields than those, of course. Fields :
'camera', 'filetype', 'filename', 'time_stamp', 'text' and a few others.

Those combined makes it impossible to have 2 matching rows.
The problem rises, when a mpeg (filetype=8) movie is entered. The filename indicates the time when it started recording, but entry in field 'time_stamp' gets "now()" as parameter. I then would like the time_stamp field to reflect on the start of recording.

Therefore a trigger on insert for a certain filetype (8), that could parse the filename and use this as time_stamp instead of the "DEFAULT"..

Hopes this makes more sense 🙂

PS: The recording program/project makes it hard to do it in the app, it just passes on some default parameters to a given database.. :bemused: